Text to Column question

[Nevsy]

each week i copy some text from a pdf onto a spreadsheet. part of this weeks text is below. when i copy it onto my spreadsheet each row is placed in one column.

i then use text to columns to sort it all out. the starting text in each row needs to be in one column and then each number in each following column.

for example, "FTSE 100" would be in column A then 14.9 in col B, 12.9 in col C, 12.2 in Col D etc. then on the next row, FTSE 100 ex financials could be in col A, 16.1 in col B, 13.5 in col C.......and this is where the problem starts.

i cant seem to find a way to put the text into columns that can capture the initial text.....if i use space as the deliminator then each of FTSE and 100 goes in different columns, and each of FTSE 100 ex financials goes into separate columns......

there will always be 5 columns of numbers and 1 column of text

any thoughts?

FTSE 100 14.9 12.9 12.2 11.4 10.8
FTSE 100 ex financials 16.1 13.5 13.0 12.3 12.0
FTSE 100 ex financials and oils 18.5 14.9 13.8 12.6 11.9
FTSE 100 ex financials, oils & mining 18.3 16.2 15.2 14.0 12.6
FTSE 20 13.6 11.9 11.5 10.8 10.4
FTSE '80' 18.1 16.0 14.4 13.0 11.8
FTSE 250 19.0 15.9 15.0 13.3 11.7
FTSE Small Cap* 28.3 20.3 17.8 14.7 12.5

 

[Lewiy]

If your data is in column A, then start with, in cell B1:

=LEFT(A1,LEN(A1)-LEN(C1&D1&E1&F1&G1)-5)

Then in C1:

=MID($A1,FIND("@",SUBSTITUTE($A1," ","@",LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-(7-COLUMN(C1))))+1,4)

And copy this one across to D1, E1,F1 and G1. It should then look like this:

Excel Workbook
	A	B	C	D	E	F	G
1	FTSE 100 14.9 12.9 12.2 11.4 10.8	FTSE 100	14.9	12.9	12.2	11.4	10.8
Sheet1

 

[Nevsy]

that is a brilliant solution Lewiy. thanks.