SubscribeSplit Text on New Line using Excel & VBA [Macros]
Hafiz, One of our avid readers, writes in.
Dear chandoo,
all the time, I use to spend time exploring chandoo.org. it’s very helpful site. thanks for your day & night efforts.
here I have to face a problem with “Text to Column”. can you please spare some time & guide me.
The problem is when I convert data from text to column using dash “-”, conversion is easy. but when the gap provided in text is with “alt+enter”, i can’t convert the data.
Do you have some solution specifically using text to column.
Well, I tried to use text to columns feature (from Data ribbon) and it would not work.
Although you can use formulas to do the splitting, they might become tedious. So the next logical option is to use macros.
Excel Macros to Split Text on New Lines
So I wrote a simple macro, that would take the text in current cell, split it and place it in adjacent cells. Like this:
Macro Code to split text on new line:
Here is the macro code to split text based on new lines.
Sub splitText()
'splits Text active cell using ALT+10 char as separator
Dim splitVals As Variant
Dim totalVals As Long
splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
End Sub
How does this code work?
- First we take the activecell’s value and split it based on Chr(10) as delimiter. This is the code for new lines.
- Then, we assign this split values to the range of cells adjacent to active cell.
- Then, we go grab a cup of coffee and sing our favorite song. Because the work is done!
Download Example Workbook
Click here to download example workbook and play with this macro. Make sure to enable macros.
How do you split text?
I really like the built-in text import feature in Excel and use it often. I use it to clean data, remove unnecessary columns or split text. In cases like this, I resort to VBA to have good control over how I want to split.
What about you? How do you split text. What is your experience. Please share your ideas and tips using comments.
Learn more about Splitting Text
If you split often, you will find this tutorial useful.
More VBA & Excel Macro Examples
If you want to learn VBA, go thru these examples
- Create PPT Slides Automatically using Excel
- Interactive Dashboards using HYPERLINKS & VBA
- Convert ISERROR formulas to IFERROR using VBA
- Merge Cells without Loosing Data
- Learn More about VBA – Join our VBA Classes
| ||
|
Spread some love,
Posts & Navigation
Tags: downloads, Learn Excel, macros, Microsoft Excel Formulas, screencasts, split, text import wizard, ubound, VBA |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
25 Responses to “Split Text on New Line using Excel & VBA [Macros]”
Chandoo, if you want to do this in the Text To Columns dialog box,
Select Delimited, then check “Other”
In the box beside Other, press the Alt key, and type (using the number keypad): 0010
That should separate the text on the line break character.
Wow… You are a genius. I tried with ALT+10 and it did not work. But your code does the trick
I’m using XL 2003 and the macro works great, but I can’t get the Text to Columns to work using Alt+0010. Should it work in 2003, or only in a newer version?
@Deb, it works in Excel 2003 too. You need to use the number keypad though, not the numbers at the top of the keyboard.
Chandoo,
Definitely the most direct way is to use Text To Columns as Debra suggested.
.
But doing the splitting in code can be done much more efficiently.
.
Sub SplitText()
Dim vA As Variant
If Len(ActiveCell) Then
vA = Split(ActiveCell, vbLf)
ActiveCell.Resize(, UBound(vA) + 1).Offset(, 1) = vA
End If
End Sub
.
vbLf is the built-in constant for the line feed in VBA. There is no need to create a string for this character on the fly with Chr(10). And by the way, Chr() produces a variant. If you were intent on creating the string on the fly instead of using the prebuilt constant, you should instead use Chr$(), which produces a string. Strings are processed faster than variants.
.
Also, please notice how this code efficiently resizes and offsets ActiveCell, saving a lot of busy work when calculating the output range.
.
I think such code would be much more useful if it operated on the whole column of inputs, similar to what Text To Columns does. Assuming all of the input cells are selected in columb B, a slight modification gets us there:
.
Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Selection.Rows.Count
vA = Split(Selection.Resize(1).Offset(i - 1), vbLf)
Selection.Offset(i - 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
Next
End Sub
.
Regards,
Daniel Ferry
Excel MVP
awesome discussion. that resize property will come in very handy for something i’m working on right now. thanks for sharing.
Another way to enter the Line Feed character into the Text-To-Columns dialog box is to simply press CTRL+J into the field next to the Other checkbox. This will enter the same character as pressing ALT+0010 on the Number Pad does.
What if there are more than 10 characters or just 5 charcters? I tried Alt+0020 or Alt+0005 but it doesn’t work. Is 10 character the only choice?
@Rick
Well there you go, I knew of Alt+0010 but had never heard of Ctrl J,
Thanx
.
@Fred
The Alt +0010 doesn’t mean 10 Characters,
It means insert a character with Character Code 0010
If you goto Insert Symbol, select Normal Text and click on the characters it will show you the Character Codes in the Dialog just above the Insert Button.
Thanks Hui.
Hi Chandoo
I’m interested on your code above, i have same case but in numeric instead of text. Between numeric separated using ” ” (space) and i change your code from alt+10 to ” ” (space)
it’s work, but the problem is i have many rows to be split, your code only for 1 cell instead of range
need your guide how to change it to range
very much appreciate for your advice
rgds,
Candra
@Candra
Have you tried using the Text to Columns Command
.
or modifying Daniels Code from above
.
Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Selection.Rows.Count
vA = Split(Selection.Resize(1).Offset(i - 1), " ")
Selection.Offset(i - 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
Next
End Sub
Thanks Hui.
that code working
rgds,
Candra
Great macro
Thanks Chandoo
Given the first message in this thread, I’m surprised no one has cast it into code. If you want a macro solution, here is a one liner that will split all the cells (in a column) at the Line Feeds within the Selection…
Sub SplitAtLineFeeds()
Selection.TextToColumns Selection(1).Offset(, 1), xlDelimited, , True, False, False, False, False, True, vbLf
End Sub
Thanks to all of you, specially chandoo for providing us a junction to share brilliant ideas to our problems.
I am very much thanksfull to Debra Dalgleish for good idea/trick
Hi while using the code I am geting Debug error “Selection.Offset(i – 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
Next”
Please help
Sub SplitTextColumn()
Dim i As Long
Dim vA As Variant
For i = 1 To Selection.Rows.Count
vA = Split(Selection.Resize(1).Offset(i – 1), vbLf)
((Selection.Offset(i – 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
Next))
End Sub
[...] this post a while back, I leapt to post my CellSplit() function in the comments, then realised that I [...]
@Rick Rothstein: Now, THAT is awesome.
Is their a way to achieve this in google spreadsheet ??
Hi,
The code works great. Does anybody know how I can join the seperate after I split them?
I have a string that looks like this: 0:0000:00:00:00:00:00:00:00:00:00 the first block can just be split and displayed, but block 2,3 and 4 need to be combined to a Date. 5,6,7 need to be Time and 8,9,10,11 need to be combined to days:hours:minutes:seconds.
Anybody have an idea how I can do this?
Thanks a lot.
Hi Chandoo,
I’m challenged with doing the inverse of what you’ve highlighted above. I need to be able to split text taken from an embedded word object (with vbcr as a delimiter) and then place the same text in a single cell of a worksheet so that it appears with the line feeds in place exactly as they do within the embedded word object. Any ideas??
i have a long concatenated data with delimiter ” | ”
TASKS |PHYSICAL STATUS | MEMORY STATUS | SYSTEM STATUS | Mkmk | Good | Full | Active | Kjkfl | Bad | Half | Inactive | Jshs | Ok | Null | Active | Aswi5 | Good | Half | Inactive | Uyusk | Bad | Full | Inactive
in cell A1. i want to split it into 4 columns as follows
TASKS PHYSICAL STATUS MEMORY STATUS SYSTEM STATUS
Mkmk Good Full Active
Kjkfl Bad Half Inactive
Jshs Ok Null Active
Aswi5 Good Half Inactive
Uyusk Bad Full Inactive
Pls code a macro for this !! Im struggling to figure it out on my own.
Thanks!
k cant really show it properly here..
but something like this:
TASKS— PHYSICAL STATUS- MEMORYSTATUS–SYSTEM STATUS
Mkmk—- Good————-Full ———Active
Kjkfl— Bad————–Half———-Inactive
Jshs—- Ok—————Null———-Active
Aswi5— Good————-Half———-Inactive
Uyusk— Bad————–Full———-Inactive
without the ‘———-’ btwn the strings
Hi, superb discussion. But I have to split the cell contents (several lines) to rows. How to do? I have tried to modify the above program, but I didn’t get the expected result.