1     Exxon Mobil     452,926.0     41,060.0
2     Wal-Mart Stores     446,950.0     15,699.0
3     Chevron     245,621.0     26,895.0
4     ConocoPhillips     237,272.0     12,436.0
5     General Motors     150,276.0     9,190.0
6     General Electric     147,616.0     14,151.0
7     Berkshire Hathaway     143,688.0     10,254.0

How do I take the above data and create four columns of data in excel? Rank# Company Name #data1 #data2

Split Text into Columns Function in Excel
this solutions comes close ...

Where is the "read line from right to left" and use the first two spaces to delimit text function?

Chers, Mw


I'd do this in two steps.

  1. The data appears to use five spaces to delimit, so I'd do a find and replace in a text editor replace any string of five spaces with a "safe" character, such as a tab or a pipe (|)
  2. Open the file in Excel, and select the appropriate delimiter or use the Data > Text to Columns

You can do this with formulas as well. In column B, copy this formula down:

=SUBSTITUTE(A2,"     ","|")

This will replace 5 spaces with | resulting in 1|Exxon Mobil|452,926.0|41,060.0

In column C, copy this formula down:

=TRIM(LEFT(SUBSTITUTE($B2,"|",REPT(" ",50)),50))

This will replace the | with 50 spaces, take the left 50 spaces, and trim padding spaces. So you end up with 1

In Column D, copy this formula down:

=TRIM(MID(SUBSTITUTE($B2,"|",REPT(" ",50)),50,50))

Instead of taking the left 50, it takes the 50 characters starting from character 50.

Column E is the same, but starting from character 100:

=TRIM(MID(SUBSTITUTE($B2,"|",REPT(" ",50)),100,50))

Column F is taking the rightmost 50 characters:

=TRIM(RIGHT(SUBSTITUTE($B2,"|",REPT(" ",50)),50))

If you don't like having the intermediate column, you can replace $B2 with the original formula: =SUBSTITUTE(A2," ","|") to skip a step.

You can also change that original formula from =SUBSTITUTE(A2," ","|") to =SUBSTITUTE(A2,REPT(" ",5),"|") which may be easier to read and diddle with if your data changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.