Text to Column question

Nevsy

New Member
Joined
Aug 13, 2006
Messages
32
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
If your data is in column A, then start with, in cell B1:
Code:
=LEFT(A1,LEN(A1)-LEN(C1&D1&E1&F1&G1)-5)
Then in C1:
Code:
=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
ABCDEFG
1FTSE 100 14.9 12.9 12.2 11.4 10.8FTSE 10014.912.912.211.410.8
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,459
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top