0
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

Community
  • 1
mario wilson
  • 1

3 Answers3

1

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
ernie
  • 6,323
0

No one's given a "magical" answer so I shall give another "slog through it" sort of answer. Mind you, it's 2021 now, so there's better to be had and I'll address that as well, but variations that work in versions available in 2013 come first.

So, if doing a full import, one can use the Import Wizard but it has unfortunate results as it won't take delimiters of two or more characters. So "Exxon Mobile" comes in as two columns instead of one. I believe Power Query was available in 2013, under one name or another, so that'd be the way to proceed with the basic import. It would've let one overcome the single character delimiter problems without a lot of hand work (after the first time in PQ, then automatically). And I'm sure it would have let one rearrange the columns even then, so...

In any case, let's consider the data from the question is IN Excel already, sitting there needing splitting and arranging.

How to split it? Since Text to Columns ("T2C") is just the Import Wizard entered at a AFTER the point at which the Import Wizard would take a file to import, that won't work any better. One COULD run it and hand shift, or formula shift, the out of column materials, but that'd suck, a lot.

So, tear it apart with formulas. Nothing fancy being proposed by me here, just a very simple, "get it into the columns it needs to be in" kind of formula set, and then quickly dealing with the arranging.

Four columns are desired and as mentioned in the first answer, five spaces is the delimiter string. This approach works as follows:

  1. Find the start position of the first of these delimiter strings. Substract 1 from it and you have the last position of the desired first string. (So if FIND() reports "2", then the last character in the first piece of the line's data string ends in that position. (I use MID() in the formula because I prefer it to LEFT() or RIGHT() unless they are demanded by the need. Personal preference, I find it is more extensible that way.)

    =MID(A1,1,FIND(" ",A1)-1)

  2. Next, "subtract that from the original string" in A1. "Subtraction" from a string is done via SUBSTITUTE() replacing the material with a blank string (""). So:

    =MID(SUBSTITUTE(A1,I1&" ",""),1,FIND(" ",SUBSTITUTE(A1,I1&" ",""))-1)

    which does the "subtraction" yielding a smaller string that starts with the next desired value, then uses the method in Step 1 to get that value.

  3. "Rinse and repeat" as the shampoo bottles say. Do the same for each other desired piece until the last one. In this case, just one more! For each, you must add to the string being substituted away. That string could then get pretty long, but that's barely daunting and no difficulty at all.

    =MID(SUBSTITUTE(A1,I1&" "&H1&" ",""),1,FIND(" ",SUBSTITUTE(A1,I1&" "&H1&" ",""))-1)

    See how the I1&" "&H1&" " string is growing?

  4. So finally, one just needs to "subtract off" the full string of things split out already and their intervening delimiter strings. Since there is no more to find, just that "subtraction" (SUBSTITUTE()) is needed, no further pieces need found by `MID().

    =SUBSTITUTE(A1,I1&" "&H1&" "&G1&" ","")

Now you have the pieces but not in "Right to left" order. You COULD have set up these formulas in, say, columns F through I, but starting in column I and moving left. But it's not the natural way for your thinking to run, so, uncomfortable in the doing, but more importantly, copying a formula to the next column doesn't necessarily adjust your cell references in a desirable manner. So I prefer still working left to right in the "natural" manner the FEW times I have something like this come up. Even though, in this case, I copied formulas from inside the Formula Editor, not cell to cell, so it might not have mattered at all. Just liked the "natural" feel.

After creating the formulas like so, just insert a cell to their left and use Drag and Drop to rearrange them in the desired (couldn't type "proper" there... "natural" and all that) order. Done. Copy the set down as many rows as needed and completely done.

Ordinarily, I like using INDEX() to reverse column order as it automates the process letting the formulas stand on their own, no hand work intervention required, but that'd require a further set of cells to be used since these formulas are independent in the sense that one cannot easily cite them as a range for INDEX(). So, living without that, and using Drag and Drop. It's only a teensy extra step anyway.

In 2021, one could simply use the FILTERXML() trick (SUBSTITUTE() for the delimiter strings the new delimiter string </Element><Element> and prepend and append the opener tags and ending tags (use "Group" as the outer tag) to get a string FILTERXML() can use). That gives you an array (a SPILL array) that you can feed directly into INDEX() and use it to reverse the columns in the array. So no Drag and Drop at the end, just let the formulas stand on their own. And since it all occurs in the single cell, spilling rightward, you can set INDEX/FILTERXML" SPILL` DOWNWARDS to take care of what "copy downward" used to be needed for. One formula to replace them all. (Sorry JRRT.)

(Now, if one wanted to reverse a string character by character, not piece by piece, that'd been a different thing yet. From the title, I thought that was the desire, actually. Well, read and learn, eh?)

Jeorje
  • 1
0

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.

jmac
  • 333