PDA

View Full Version : split alt+enter values of a cells into multiple cells



bernandas
06-01-2015, 06:22 AM
dear all,

pl help me with macro for the following scenario. want to put the alt+enter values of same cells into multiple cells. have given base file and base file with required output. kindly do the needful.
13572

Kenneth Hobs
06-01-2015, 06:41 AM
Welcome to the forum!

Add this to a Module, select you cells to parse, and run the macro.

Sub SplitSelectionByVBLFToRight() Dim c As Range, s() As String
On Error Resume Next
For Each c In Selection
s() = Split(c, vbLf)
c.Offset(, 1).Resize(, UBound(s) + 1).Value = s()
Next c
End Sub

p45cal
06-01-2015, 07:41 AM
You can also use Text-To-Columns, either programmatically or manually:
Programmatically, select your cells first then run:
Sub blah()
Selection.TextToColumns Destination:=Selection.Offset(, 1), OtherChar:=vbLf
End Sub
Where the 1 in .Offset(,1) means 1 cell to the right of the source cells, change that to whatever number you want, or if you're content to have the result overwrite the source cells as is the default in text-to-column operations, then this shorter verion will do:
Sub blah()
Selection.TextToColumns OtherChar:=vbLf
End Sub

Manually: Select your source cells, bring up the text-to-columns dialogue box (Data tab, Data Tools section) and in step 1 choose Delimited, in step 2 choose only Other: and put the text cursor in the field and on the keyboard, hold down the Alt key and tap on the number keypad 0010, then let go of the Alt key, then in step 3 choose your destination if you need to and press Finish.

p45cal
06-01-2015, 07:48 AM
Oh groan, you cross posted this here: http://www.mrexcel.com/forum/excel-questions/858420-split-alt-enter-values-cells-into-multiple-cells.html
Please read http://www.excelguru.ca/content.php?184 and with netiquette in mind, please 'do the needful' there and anywhere else you may have cross-posted to.