Split numbers and text from a single Cell

**wong_lizzie** · 12-17-2012, 12:26 AM

Hi,

I need help with splitting some information from a single text.

Sample data is as follows:

1570.0 LIST S B1570.0 SELL SG
1965.0 LIST S B1965.0 SELL SA
1788.8 LIST CS1788.8 SELL DO
1715.0 LIST S B857.5 SELL S B857.5 SELL S B
1480.0 LIST S B1480.0 SELL SS
2060.0 LIST S B2060.0 SELL DO
1312.19 LIST DO1312.19 SELL DO

I need to split them so that I have the information in different columns as shown below:

1570.0 SB 1570.0 SG
1965.0 SB 1965.0 SA
1788.8 CS 1788.8 DO
1715.0 SB 857.5 SB 857.5 SB
etc...

Would really appreciate if anyone could help!!

Thanks,
Liz

**FDibbins** · 12-17-2012, 12:51 AM

have you tried using text-to-columns?

**:) Sixthsense :)** · 12-17-2012, 01:06 AM

Not sure about all your data and it's structure, so check whether this helps!

A1 cell
1570.0 LIST S B1570.0 SELL SG

B1 cell

Formula:

Please Login or Register  to view this content.

**wong_lizzie** · 12-17-2012, 02:25 AM

Hi,

text-to-columns won't work as they are not properly spaced out.

**FDibbins** · 12-17-2012, 02:32 AM

1570.0 LIST S B1570.0 SELL SG
1965.0 LIST S B1965.0 SELL SA
1788.8 LIST CS1788.8 SELL DO
1715.0 LIST S B857.5 SELL S B857.5 SELL S B
1480.0 LIST S B1480.0 SELL SS
2060.0 LIST S B2060.0 SELL DO
1312.19 LIST DO1312.19 SELL DO

I need to split them so that I have the information in different columns as shown below:

1570.0 SB 1570.0 SG
1965.0 SB 1965.0 SA
1788.8 CS 1788.8 DO
1715.0 SB 857.5 SB 857.5 SB

i used TTC - delimited/space and all but 2 came out fine.

i would suggest you upload a sample workbook, rather than trying to copy/paste your data here

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**wong_lizzie** · 12-17-2012, 02:33 AM

Book1.xlsHi Sixthsense,

Thank you for the suggestion.

I have used that previously along with formulas such as:
=LOOKUP(9.999999999E+307,--("0"&MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0 123456789")),ROW($1:$20))))
etc.

I have attached a copy of the excel file. "What I need" tab specifies the format that I need them to be in, and "What I have done" shows multiple formulas that I have done to derive to the format I need (the columns in yellow).

But what i've done is very tedious and not accurate.

If anyone could assist me, or could advise if there is any easier or better way to split the amount from the alphabets, that would be great.

**wong_lizzie** · 12-17-2012, 02:35 AM

Thanks FDibbins.

I have just attached the fileBook1.xls. Really appreciate your help!

**Marcol** · 12-17-2012, 08:12 AM

Your data, as I'm sure you're aware, is somewhat erratic.

I'm not sure that this is any better than what you already have, but it might give you some ideas to work with.