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.

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.

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.

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.