How to split text

jeffblack

New Member
Joined
Dec 2, 2016
Messages
3
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Code:
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
 
Last edited:

jeffblack

New Member
Joined
Dec 2, 2016
Messages
3
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

Active Member
Joined
Nov 6, 2014
Messages
319

ADVERTISEMENT

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_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Code:
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
 
Last edited:

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572

ADVERTISEMENT

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">String</td><td style=";">First</td><td style=";">Second</td><td style=";">Third</td><td style=";">Fourth</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1/2/3/4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">"/",A2</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,"/",REPT(<font color="Purple">" ",100</font>)</font>),100,100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,"/",REPT(<font color="Purple">" ",100</font>)</font>),200,100</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A2,"/",REPT(<font color="Purple">" ",100</font>)</font>),300,100</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
You can do it with one formula
Enter in B1 and copy across

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

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">1/2/3/4</td><td style="font-weight: bold;text-align: right;font-style: italic;color: #FF0000;;">1</td><td style="font-weight: bold;text-align: right;font-style: italic;color: #FF0000;;">2</td><td style="font-weight: bold;text-align: right;font-style: italic;color: #FF0000;;">3</td><td style="text-align: right;font-style: italic;color: #FF0000;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,525
Messages
5,659,327
Members
418,497
Latest member
VAllen79

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