Extracting numbers from cells with text

[manc]

Dear Demigods,

In cell A1 I have "120x50x120"

In B1 I have the formula =LEFT(A1,FIND("X",A1)-1)

This extracts the first numeric value before the first "X".

The numerical values can be two to three numbers big.

How can I extend the formula so that it extracts the first number into B1, second number into C2 and third number into D2?

The end user may also enter A1 as "120 x 50 x 120", so I have to take the spaces into consideration also.


Your help, as always, is greatly appreciated.
Best Regards
manc

 

[Rick Rothstein]

I presume you meant to type C1 and D1 instead of C2 and D2. Here is a different way to do what you want with a single formula. Put this formula in B1 and copy it across to D1, then copy B1:D1 down as far as you need to...

=TRIM(MID(SUBSTITUTE($A1,"x",REPT(" ",99)),COLUMN(A1)*99-98,99))

 

[Pup Denab]

HI Manc
This is the easiest way, text to column wizard, just use other as ' x ', this will do what you ask, it uses the x to separate the columns
Regards
Pup

You will find the wizard in the data tab

 

[manc]

Dear Mr Rothstein,Again, genius.

That's two out of two you have hepled with, and both counts answered in such prompt fashion. Your suggestion works great - thank-you for your time and effort, it is appreciated.

Unfortunately Pub Denab, I couldn't read your post as it did not format correctly, but thankyou for your time.

Best regards
manc

*note to self* next time, try really obscure time, to see how fast Mr Rothstein really is.

 

[manc]

SOLVED Re: Extracting numbers from cells with text

amending thread to SOLVED

 

[Rick Rothstein]

Unfortunately Pub Denab, I couldn't read your post as it did not format correctly, but thankyou for your time.

Actually, Pup's suggestion is a very good one if you do not need the immediate updating of the split out numbers (that is, once they are split apart, nothing will change). If that is the case, then here is more complete details for the Pup's suggestion. Select Column A, press ALT+de to bring up the Text-To-Columns dialog box, select Delimited on the first step and then press Next, put a check mark in the checkbox labeled "Other" and type an x into the blank box next to it, then click Next, change the Destination field to $B$1 and then press the Finish button.

*note to self* next time, try really obscure time, to see how fast Mr Rothstein really is.