Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling text from a cell
I copied some data down to excel from the internet (no .xls or .csv
download available, just a straight copy and paste, unfortunately). The data is in the format below: 5,503,522 7,268,551 There is a space between the numbers. I have used right, left, find and len formulas in the past to separate text like this, but I'm having trouble with this one. Assuming the above data was in cell A1, I initially tried using the formula =RIGHT(A1,FIND(" ",A1)) However, this returns 551. I checked this and running FIND(" ",A1) returns a value of 3. This doesn't make sense to me either, but if anyone could explain a solution or why the FIND formula I was using was returning that particular value, I would appreciate it. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling text from a cell
With that value in A1, using =FIND(" ",A1) in cell B1, I got the value 10.
Then using =MID(A1,B1+1,99) in cell C1, it extracted the remainder of the numbers you wanted. (99 was just a number picked out of the air as the amount of characters needed to extract). Saruman "jnasr" wrote in message ups.com... I copied some data down to excel from the internet (no .xls or .csv download available, just a straight copy and paste, unfortunately). The data is in the format below: 5,503,522 7,268,551 There is a space between the numbers. I have used right, left, find and len formulas in the past to separate text like this, but I'm having trouble with this one. Assuming the above data was in cell A1, I initially tried using the formula =RIGHT(A1,FIND(" ",A1)) However, this returns 551. I checked this and running FIND(" ",A1) returns a value of 3. This doesn't make sense to me either, but if anyone could explain a solution or why the FIND formula I was using was returning that particular value, I would appreciate it. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling text from a cell
Try...
=LEFT(A1,FIND(" ",A1)-1) and =MID(A1,FIND(" ",A1)+1,1024) To return numerical values, try the following instead... =LEFT(A1,FIND(" ",A1))+0 and =MID(A1,FIND(" ",A1),1024)+0 Hope this helps! In article . com, "jnasr" wrote: I copied some data down to excel from the internet (no .xls or .csv download available, just a straight copy and paste, unfortunately). The data is in the format below: 5,503,522 7,268,551 There is a space between the numbers. I have used right, left, find and len formulas in the past to separate text like this, but I'm having trouble with this one. Assuming the above data was in cell A1, I initially tried using the formula =RIGHT(A1,FIND(" ",A1)) However, this returns 551. I checked this and running FIND(" ",A1) returns a value of 3. This doesn't make sense to me either, but if anyone could explain a solution or why the FIND formula I was using was returning that particular value, I would appreciate it. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling text from a cell
Doesn't give that result for me. Try copying & pasting the data from the
back into A1. But did you really want that formula? In your RIGHT() expression you're using an argument which is the position of the space, measured from the left. Try in with different length strings before & after the space. If you want the right-hand side you'll need =RIGHT(A1,LEN(A1)-FIND(" ",A1)). If you want to separate the data, you could of course just use Data/ Text to Columns -- David Biddulph "jnasr" wrote in message ups.com... I copied some data down to excel from the internet (no .xls or .csv download available, just a straight copy and paste, unfortunately). The data is in the format below: 5,503,522 7,268,551 There is a space between the numbers. I have used right, left, find and len formulas in the past to separate text like this, but I'm having trouble with this one. Assuming the above data was in cell A1, I initially tried using the formula =RIGHT(A1,FIND(" ",A1)) However, this returns 551. I checked this and running FIND(" ",A1) returns a value of 3. This doesn't make sense to me either, but if anyone could explain a solution or why the FIND formula I was using was returning that particular value, I would appreciate it. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aligning Wraped Text to the bottom of a cell | Excel Discussion (Misc queries) | |||
linking a cell containing text to another cell containing text / data | Excel Discussion (Misc queries) | |||
Text not continuing to wrap for large block of text in Excel cell | Charts and Charting in Excel | |||
Cell Capacity - text | Excel Discussion (Misc queries) | |||
How can I make an excel cell equal to the value of a frame object text box | Excel Worksheet Functions |