Parse multiple values from a single cell into seperate rows

[gmoorthy]

I have an excel spreadsheet which is like this:

ABC
XYX

The above mention is all in one single cell . How can i parse this and seperate them out into multiple cell.

 

[SkipVought]

Hi,

First determine the ASCII value of the 4th character.

You could record a Replace feature and them modify the code to replace that ASCII character with some other character like |.

Then use Data > Text to Columns to parse into adjacent columns.

 

[madonnac]

you may need to adjust the vbLf constant, depending on the values that force the new line

vbLf is the LineFeed character, chr(10)
vbCR is the Carrage Return character, chr(13)
vbCrLf is both combined into one
These selections cover 95% of the cases.

Create a UDF function:

Function GetLine(data As String, n As Long)
GetLine = Split(data, vbLf)(n - 1) 'subtract 1, as array from the Split function are 
                                   'zero based, and most people count lines from 1
End Function

and call using =GetLine(A1,2) as the formula to get the second line from the cell A1