I have some data that has names but is not consistant i.e
Smith, John
Smith,John
I want a formula that says, if there is a space after the comma do nothing, otherwise insert a space between.
I have figured out the second part so my formula will look like
=if(some logic to identify a space after the comma ,"",already figured out this bit)
I want to sort a spreadsheet based on whether any of the numbers '1', '2' or '3' appear in a particular column.
There are 2 particular rows which I care if they have a '1', '2' or '3' in them, let's say row 17 and 18.
I want to create a formula say in row 20 which would tell me if there is a '1', '2' or '3' in the corresponding cells in EITHER (!) row 17 or 18.
An example to clarify:
Cell A17 = 1
Cell A18 = 2
Then Cell A20 would be = 1 because both A17 and A18 have a '1', '2' or '3' .
2nd example:
Cell C17 = 1
Cell C18 = 0
Then Cell C20 would be = 1 because C17 has a '1', '2' or '3' .
3rd example
Cell D17 = 6
Cell D18 = 12
Then Cell D20 would be = "" (Null - no value) because neither D17 nor D18 has a '1', '2' or '3' .
Note: I am looking just for the numbers '1', '2' or '3' not (!) numbers like 11, 12, 13, 21, 22, 23 etc...
Sheet 1, column A, has invoice numbers from 1 to whatever is needed for the month.
How can I get Sheeet 2 (the following month’s) to automatically start numbering from wherever Sheet 1 leaves off?
I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.
--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.
--Is there a function/filter that would be able to report the highest number recorded?
I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?
View 1 Replies View RelatedIs there a way to reference a cell value instead of using a static value for col_index_num? For example - Instead of telling it to look in the 5th column of Sheet2, I would like it to look in the column were the heading (row 2) in Sheet2 is equal to the value of O2 in Sheet1.
Here's another example -
Normal formula: VLOOKUP(a2,Sheet2,5,FALSE)
What I'd like: VLOOKUP(a2,Sheet2,column where row 2 equals the value in 'Sheet1'!O2, FALSE)
As i am working on consolidating the Missing numbers manually, whether will it possible for making this automatic.
In Column "A" I have Multiple Duplicate Invoice Numbers and followed by the Different Transcation numbers in Column "B".
In Column "D" I have Each Invoice Numbers and in Column "E" Total Transaction Numbers i.e., no. of times each Invoice have Transactions.
In Column "F" No. of times we received the Transactions.
Here i need to manually need to update each Invoice's Missing Transaction number in Column "H" in single cell with comma separated to each Missing Transaction numbers.
Example:
Before : (Column "A" & "B")
Invoice NumbersTransaction NumbersInvoice NumbersTotal Transaction NumbersReceived Transaction Numbers
A2014000351501 A201400035150 3 2
A2014000351502 A201400035494 13 12
A2014000354941 A201400035881 12 11
A2014000354942 A201400035884 19 17
A2014000354943 A201400035888 14 13
A2014000354944 A201400037613 5 1
A2014000354945
[Code]...
Instead of doing this manually can we have the VBA to auto Update these missing numbers.
Here by i am attaching sample file.
Using Excel 2007. I have a limit of 80 characters and spaces in a particular cell and I need to force those characters/spaces over 80 to the next cell. Is that possible?
View 11 Replies View RelatedI have columns that are defined with these width's:
A - 9B - 1C - 12D - 12
I need a macro that would add spaces to the right of the cell if the cell does not have number of characters of the lenght of the column width.
For instance:
Cell A1 has 5 characters so it would need 4 spaces.
Cell A2 has 7 characters so it would need 2 spaces.
Cell B1 has 1 character so it would NOT need a space.
Cell B2 has 0 characters so it would need 1 space.
I need to do a lookup on these cells but for some reason they all appear to have some kind of space characters in each cell which you can see if you double click on them. I have tried trims, text to columns, find replaces and none of these seem to work
I have a huge list of these numbers / codes and they all have the weird space kind of characters at the end
Can anyone give me a solution on how to remove them so my lookup will work?
I'm trying to match in the example, 'Name 1' which will pick the number below name 1. However if I have any space before, ' Name 1' in cell A1 than it won't match. I've put in wildcards in the formula but it still doesn't pick up the space? I need the formula to pick up either no spaces, or any number of spaces before or after 'Name 1'.
[Code] ......
I've attached an excel file showing the layout...
test_match.xlsx‎
I'm trying to add rows based on the numeric value in column I. For an example, if I2 value is 2, add 2 rows below row 2 and keep the same interior color. Is it possible???
View 2 Replies View RelatedI have a column with 11 digit numbers formatted as text (Column C) and it seems like there's three spaces after the number (In Sheet A). I am trying to do a vlookup comparing Sheet A to Sheet B. It's not working and not sure if it's the extra spacing in the cell of Sheet A that's causing the Vlookup not to work.
I would like to compare both lists to see what UPC's are on both sheets. Any ideas how I can do this?
In Sheet A, Column C contains UPC (11 digits) 07845968952
In Sheet B, Column E contains UPC (11 digits)
I know how to pad cells with zeros for a numeric field. I can't figure out how to custom format a cell to pad a text field with spaces.
If I want description to have 10 characters, if they enter BLUE I want it to be BLUE then 5 spaces to make a total of 10) if they enter RED I want it to be RED with 7 spaces to make a total of 10.
With a number, I can just enter 00000 and if I enter 4, it will be 00004 Can you do this type of thing with a text? I can figure out a formula to get what I need, but I want a way to do it directly into the cell when the user types it in.
I have a column with a possible value of 1 to 7. The value represents the day of the week. I would like the value to be displayed in such a way that it is on the right position in relation to other days. So day one is a 1 at the first position, day 2 will be a space and then a 2, day 3 will be 2 spaces and then a 3 etc etc.
View 3 Replies View RelatedHow to copy part of a cell to a new cell when there are no spaces or specific features to use in the formula. My cell contains a data and time as such: 2013-10-22T14:05:34Z
My hope is to keep the data 2013-10-22 in one column and transfer the 14:05:34 to a new column...if the 'T' and 'Z'
How do i delete an empy spaces in cell. For example i have this value:
33.1 x 2.9 and i want to have: 33.1x2.9
I am trying to get rid of some extra spaces at the end of a word in a cell.
As shown in the following code I have tried different functions to get remove them, but they are not working. Are those not spaces?
The cell has this word : "LPPJ4K2**" ' in excel the * appear as a white space
Code:
Filler = "LPPJ4K2**" ' this word is actually is in a cell
Filler = Trim(Filler) ' Not working
Filler = Application.Trim(Filler) ' Not working
Filler = Replace(Filler, " ", "") ' Not working
Any easy way to remove all spaces from a cell, both leading and trailing? I find it hard to believe that Excel doesn't have this functionality. I don't particulary want to write a VBA script since I have never done it but if that's the only way, I'd love to know how to write it. I have looked everywhere but obviously not in the right places.
View 4 Replies View RelatedI am looking to subtotal specific cells. Basically it is a download from a data base and I have designed an input area for other users to input their predicted figures. But I am trying to put an automatic subtotal for each area depending on the spaces at the start of the cell. I have got a very longwinded formula to get the right subtotals in other files by categorising each line in another file. But I am looking for a vba code to put a simple sum() or sumif() in the relevant areas. I have included some of the categorisation I have had to use for my other formulas.
View 3 Replies View RelatedI am attempting to enter a code for the current date (m/d/yyyy). For appearance purposes only on the form I am using,
I am trying to enter spaces between the numerals. (" "&m&" "," "&d&" "," "&yyyy&" ".
I can only get the serial numbers to come up, no matter how I try to format the cell.
I copied some data off the web and there are a ton of cells with Currencies that I would like to take out the spaces infront of the numbers. Because without doing so, I am unable to use them in formulas.
so I would like to convert this ' $52,000' to '$52,000'
I tried the Trim(Cell) but that doesn't seem to work, this data does not appear to be text. I can't even change the format on it, doesn't make a difference.
I need VBA to remove more than 3 blank spaces after the city & replace them with a comma & one blank. For example:
All in one column AB starting with row 7 I have:
Johnson City (13 Blank Spaces) NC 75503 (1 to 3 spaces after state & is OK)
Jacksonville (10 blank spaces) Fl 23854
Henderson (12 blank spaces) NH 28363 (12 blank spaces)
etc
I need all in one column AC starting with row 7:
Johnson City, NC 75503 (,space before state, same space after state ok)
Jacksonville, FL 23854
Henderson, NH 28363
etc
I am trying to copy form sheet1!A1 into sheet2!A1.
Now, I want the data in sheet2!A8 to be what is in sheet1!A2 in the same order. So sheet2!A15 will have data from sheet1!A3 and so forth.
cell A1 has " HH001 " (without "", there is space infront and behind the data),
cell B1 has "motor gear case",
what formula to put in cell C1 to get like this "HH001" (removed spaces in A1)
and D1 to get like this, "[HH001] motor gear case"
I am in need of 2 validation macros (formulas) to enter into the validation criteria formula box when creating a custom validation.
1. A formula that will generate an error if there is a space in the text entry. Example:060107_Halo3CG prev.mpg would be an invalid entry due to the space after CG.
2. The same formula as above but this one also needs a 50 character limit added to the criteria.
I've got a single column worksheet with a varying numbers of characters in each row.
At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.
I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:
Dim lngSpacesNeeded As Long
For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i
Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".
I need to clear a value in a cell(what ever may be the value) based on a condition in another cell and set it to blank. Ex: If cell A is blank and then i need make cell B also blank, if cell A has some value then i should not disturb B I need to do this using formula. I have tried the below, but no use, IF(AA1="",REPLACE(Y1,1,10,""))
View 2 Replies View RelatedBasically, let's say in Rows(1), I have:
A1 = Apple
B1 = Peach
C1 = (empty)
D1 = Orange
...
Now, assuming that I don't know C1 (the cell location), but I want to insert something next to it, or for the entire column, will I be able to identify the cell by the "name" of the cell next to it???
I know where "Peach" is, and I have already inserted a blank column next to "Peach" 's column, and now I want to name the first cell of the inserted column
is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.
View 2 Replies View Related