Extracting numbers from a cell with carriage returns
 

I'm looking for the formula that would extract the numbers in a cell
like below in to three seperate cells:

This is the contents of one cell:
_______
| 3.3 |
| 9.5 |
| 12.5 |
-----

I know "=LEFT(A1,3)" will give me 3.3 and "=RIGHT(A1,4)" will give me
12.5

but....what if 3.3 was 10.3? My formula would only give me 0.3.

And... I need to extract 9.5 somehow (which could be -X.X or XX.X)

Is there a way to count text strings before and after carriage
returns???
spaces might work in this case, too...


--
kaytoo
------------------------------------------------------------------------
kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057
View this thread: http://www.excelforum.com/showthread...hreadid=538210

Extracting numbers from a cell with carriage returns
 

Insert a number of empty columns to the right, select the column, do
datatext to columnsselect delimited, click next, select other and click in
the other box, hold down alt key while typing 010 on the numpad, release the
alt key, click finish. Then copy the newly extracted numbers and do
editpaste special and select transpose to paste them into one column


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"kaytoo" wrote in
message ...

I'm looking for the formula that would extract the numbers in a cell
like below in to three seperate cells:

This is the contents of one cell:
_______
| 3.3 |
| 9.5 |
| 12.5 |
-----

I know "=LEFT(A1,3)" will give me 3.3 and "=RIGHT(A1,4)" will give me
12.5

but....what if 3.3 was 10.3? My formula would only give me 0.3.

And... I need to extract 9.5 somehow (which could be -X.X or XX.X)

Is there a way to count text strings before and after carriage
returns???
spaces might work in this case, too...


--
kaytoo
------------------------------------------------------------------------
kaytoo's Profile:
http://www.excelforum.com/member.php...o&userid=34057
View this thread: http://www.excelforum.com/showthread...hreadid=538210

Extracting numbers from a cell with carriage returns
 

That is helpful, but i will be doing this for many, many cells...

I found this formula to get me the first number:
=LEFT(A1,FIND(".",A1)+1)

I'm working on a =MID formula for the middle that counts from the
period after the carriage return...

I found you can do this:

=MID(C43,(FIND("
",C43)+1),3)

I just need to figgure out how to make the "3" a Find formula that
finds the "." after the carriage return


--
kaytoo
------------------------------------------------------------------------
kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057
View this thread: http://www.excelforum.com/showthread...hreadid=538210

Extracting numbers from a cell with carriage returns
 

That's the whole point, you can do a whole column at once

Peo

"kaytoo" wrote in
message ...

That is helpful, but i will be doing this for many, many cells...

I found this formula to get me the first number:
=LEFT(A1,FIND(".",A1)+1)

I'm working on a =MID formula for the middle that counts from the
period after the carriage return...

I found you can do this:

=MID(C43,(FIND("
",C43)+1),3)

I just need to figgure out how to make the "3" a Find formula that
finds the "." after the carriage return


--
kaytoo
------------------------------------------------------------------------
kaytoo's Profile:
http://www.excelforum.com/member.php...o&userid=34057
View this thread: http://www.excelforum.com/showthread...hreadid=538210

Extracting numbers from a cell with carriage returns
 

That is the result I want.. there isnt a formula for that?

I want to avoid doing that process everytime I download a worksheet.


--
kaytoo
------------------------------------------------------------------------
kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057
View this thread: http://www.excelforum.com/showthread...hreadid=538210