CODEWITHSUNDEEP
excelexcel-formula
pshoeg
pshoeg

Reputation: 389

Split cells in several columns with Excel formulas

I have a cell with the following content:

     A
1    text1;text2;text3;text4;text5

I'd like to divide it into five cells, one for each occurrence of ;, like this:

     B      C      D      E      F
1    text1  text2  text3  text4  text5

I know Excel has a "Text to columns" function, but I need this to be automated, so that's not an option. I've entered this in B1:

=left(A1;FIND(";";A1;1)-1)

This gets me text1, which is what I want. For the next cell I need text2. I've tried this:

=right(A1;len(A1)-len(B1)-1)

My sheet now looks like this:

     B      C
1    text1  text2;text3;text4;text5

My issue now is, that I need to remove everything after the first ;, but I can't seem to figure it out. Any ideas?

Upvotes: 0

Views: 889

Answers (3)

Zediiiii
Zediiiii

Reputation: 790

Here is a way to overcome the character limits given by the cell formula above.

  1. Create a helper row in B1 to (as far right as you like) that lists the numbers from 0 to a safe "maximum" number of list elements.

  2. Place you comma separated lists into A2:A, and concatenate a single comma to the end of the cell, for example ='sheet 1'!A1&","

  3. Row B will deal with the first list item. Use the following in B2

    =LEFT($A2,FIND(",",$A2,1)-1)

  4. Row C and to the right will separate the rest of the elements. Use the following formula in C2 where ~ is a unique character that will NEVER be used, and where the comma in the second parameter of each instance of SUBSTITUTE($A2,","... is the character you are splitting on.

=IFERROR(MID($A2,FIND("~",SUBSTITUTE($A2,",","~",C$1))+1,FIND("~",SUBSTITUTE($A2,",","~",D$1))-FIND("~",SUBSTITUTE($A2,",","~",C$1))-1),"")

  1. Adjust the offsets after the find() functions +1 and -1 to match spaces or other offsets if needed.
  2. Fill to the right from C2 over and then fill everything down from B2 over. Done!

Example Demo Image

Breakdown:

MID($A2,  [PART 1]  ,  [PART 2]  )

The first parameter $A2 is the cell with the delimited text, the second is the number of characters for the nth instance of the delimiter (ie, the starting position minus one of the nth list item), and the third is the number of characters between the n+1 and the n list item.

[PART 1]

FIND("~",SUBSTITUTE($A2,",","~",C$1))+1

First let's break down the inside substitute part:

SUBSTITUTE($A2,",","~",B$1)

The first parameter is the text that you are splitting. The second parameter is the delimiter, a comma in this example. The third parameter is a random unique character (use something like char(160) if you need something truly unique), and the last parameter is which occurrence of the delimiter (`","' in this case) you are replacing.

FIND("~",SUBSTITUTE($A2,",","~",C$1))+1

Adding find to the start of the substitute function above will find the position number of the unique character ("~" in this example) that you just substituted, effectively telling you where the "starting position" of the second list item is. For convenience, this approach addressed the first list item separately because the first delimiter is after the first list item. The +1 is to ensure the left side delimiter position itself is not included.

[PART 2]

FIND("~",SUBSTITUTE($A2,",","~",D$1))-FIND("~",SUBSTITUTE($A2,",","~",C$1))-1)

This finds the length of the list item by getting the n+1 delimiter position and subtracts it from the n delimiter position, then subtracts 1 because the ending delimiter position itself should not be included.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

If you want a formula equivalent to Text to Columns and A1 contains the text to be parsed, then in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:B)*999-998,999)) and copy across.

Upvotes: 1

WGS
WGS

Reputation: 14179

Recording a macro while doing Text to Columns would have done the trick. ;)

My record:

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
End Sub

After cleanup:

Sub SplitAndScatter()
    With Range("A:A")
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Semicolon:=True
    End With
End Sub

Set-up:

enter image description here

Result:

enter image description here

Hope this helps! :)

Upvotes: 0

Related Questions