Extracting numbers from a cell with carriage returns

  • Thread starter Thread starter kaytoo
  • Start date Start date
K

kaytoo

I'm looking for the formula that would extract the numbers in a cel
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 m
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 carriag
returns???
spaces might work in this case, too..
 
Insert a number of empty columns to the right, select the column, do
data>text to columns>select 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
edit>paste special and select transpose to paste them into one column


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
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
 
That is the result I want.. there isnt a formula for that?

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