Extracting numbers from a cell with carriage returns

[kaytoo]

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...

[Peo Sjoblom]

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



"kaytoo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[kaytoo]

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

[Peo Sjoblom]

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

Peo

"kaytoo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[kaytoo]

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

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