Text to Columns (Numbers Only?)

[agerrard]

Hi All,

I have a really large list of items (about 15000). A sample of some of the descriptions that i have:

OIL ENG 5L CASTROL 10W/40 MAGNATEC@ - 1733
OIL ENG 5.5L GTX MOD ENG 15W-40 CASTROL - 280130
DEGREASER AERO 400G EXPORT@# - 1196
OIL ENG 5L GTX 20W-50 SN CASTROL - 280132
OIL ENG 5L VALVOLINE XLD PREM 20W/50@# - 1350

What i want to do is to separate the NUMBERS from the description and put them into another column. I tried to do LEFT and RIGHT but they descriptions are not all the same so that doesn't work.

I tried doing text to columns using the "-" as the separator and whilst that did split out the numbers it still put them in different columns (i need them to all in the same column for ease of use)

Any ideas please?

 

[Trebor76]

Hi agerrard,

Try this - just change the starting cell reference from A2 to suit:

=VALUE(MID(A2,SEARCH(" - ",A2)+3,255))

HTH

Robert

 

[Rick Rothstein]

When you have text with more then one number in it, and you request to pull out the "numbers" (plural), you should really make clear which numbers. Guessing you mean just the numbers after the last dash, try this formula...

=IF(A1="","",--TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)))

 

[agerrard]

Sorry, my apologies. yes the "numbers" i wish to separate out are the ones at the very right of the description (after the last dash).

 

[agerrard]

Rick, thank you so much. That formula you gave is a winner. Saved me hours of work! Cheers!!

 

[Rick Rothstein]

Rick, thank you so much. That formula you gave is a winner. Saved me hours of work! Cheers!!

You are welcome. Just so you know... as long as your last dash is always surrounded by spaces, Robert's formula will work as well (although you would need to put the IF function housing I used around it if you plan on copying the formula down through blank cells in anticipation of future data). My formula does not require any spaces around the dash in order to work.