How to split text

[jeffblack]

Hello,

I'm looking for a formula that will allow me to split text into 4 rows, text is always in A1 in the format "1/1/1/1" but the numbers could be any length. I've tried righ and left but where the number lengths change it doesn't work for me.

 

[CyrusTheVirus]

Welcome to the forums.

First select the cells, then up at the top click the 'Data' tab and the click 'Text to Columns', then make sure 'Delimited' is selected then hit 'Next', then uncheck 'Tab' and check 'Other' and type in / in the box to the right of 'Other', then hit finish.

 

[Tim_Excel_]

Sub split()
Columns("A:D").Insert
   Columns("A:A").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2)), _
        TrailingMinusNumbers:=True
End sub

Try this, it looks for the "/" and splits for that

 

[jeffblack]

Thank you for the reply, I was looking for a formula so I wouldn't have to do text to columns every time the data changes.

 

[Declamatory]

Then to get them into rows highlight the data then right click and copy the data. → Right click on cell A2 and paste special → Transpose it will move the data from cells A1 B1 C1 and D1 to A2 A3 A4 A5

 

[Tim_Excel_]

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(2, 1), Array(2, 2), Array(2, 3), Array(2, 4)), _
        TrailingMinusNumbers:=True
        Range("A2") = Range("B1")
        Range("A3") = Range("C1")
        Range("A4") = Range("D1")
        Range("B1:D1").Delete

Watch out as this meddles with the cells around and thus only works on nearly empty worksheets, unless you dont mind data going missing.
Tell me if this helped you out

 

[63falcondude]

If you are looking for a formula, how about something like this:

Excel 2010
	A	B	C	D	E
1	String	First	Second	Third	Fourth
2	1/2/3/4	1	2	3	4
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("/",A2)-1)
C2	=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",100)),100,100))
D2	=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",100)),200,100))
E2	=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",100)),300,100))

Source: Extracting a Substring Between Second and Third Delimiters [SOLVED]

 

[jeffblack]

That is perfect thank you

 

[63falcondude]

That is perfect thank you

Glad to help.

 

[AlKey]

You can do it with one formula
Enter in B1 and copy across

=TRIM(MID(SUBSTITUTE("/"&$A1,"/",REPT(" ",99)),99*COLUMNS($A:A),99))

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	1/2/3/4	1	2	3	4
Sheet1