Separate multiple values in a cell into separate cells

[jerryrobbins]

I was given a spreadsheet of contacts in which each individual's multiple phone numbers are in the same cell separated by a linefeed. I need to separate the phone numbers into separate rows.


There at 10,000 rows in the file.

How do I do this?


Many thanks!

 

[jason]

What do you mean by a 'linefeed'?

 

[jason]

If it were me, and without seeing the actual spreadsheet.... i would:

(i have Excel 2010)


select the cell(s)

Data Ribbon - Data Tools Group

Select 'Text To Columns'

Select Next

Uncheck the Tab Box

Set the "delimiter" (this would be the linefeed (if thats a character)

Select Finish


again, this may not work depending on the setup of your data, the 'linefeed' that you speak of....

 

[SirJB7]

Hi, jerryrobbins!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Try with this code, assuming your data is in column A, and columns B in advance are empty:

-----

[pre]

Option Explicit

Sub SplittingLF()
' constants
' declarations
Dim I As Long, J As Integer, K As Integer, L As Integer
Dim A As String, B As String, rng As Range
' start
Set rng = Range("A:Z")
' process
With rng
I = 1
Do Until .Cells(I, 1).Value = ""
A = Cells(I, 1).Value & vbLf
J = 0
K = 1
Do
L = InStr(J + 1, A, vbLf)
If L > 0 Then
B = Mid$(A, J + 1, L - J - 1)
K = K + 1
.Cells(I, K).Value = B
J = L
End If
Loop While L > 0
I = I + 1
Loop
End With
' end
Beep
End Sub

[/pre]
-----


Regards!

 

[Luke M]

Here's a macro that's faster & shorter (sorry SirJB7, I couldn't resist tackling this after seeing all those well named variables =P )

Select all the data you want to split, then run macro.

[pre]

Sub BetterMacro()
Dim xNumber As Variant
Dim i As Integer
Dim c as Range

For Each c In Selection
xNumber = Split(c.Value, Chr(10))
For i = LBound(xNumber) To UBound(xNumber)
c.Offset(0, i + 1) = xNumber(i)
Next
Next
End Sub

[/pre]
Or, what might be faster, is to first create a helper column with this formula:

=SUBSTITUTE(A2,CHAR(10),";")

Copy the helper column, paste as values, and then do a Text-to-columns with the semicolon as the delimiter.

 

[SirJB7]

Hi, jerryrobbins!

Definitively I'd rather use Luke M's macro. So you should do.

Regards!


@Luke M

Hi!

Gotcha with the variable names... but I thought and guessed that the problem was a once-time process... so why typing more than one letter for each variable name?... Here's winter time and cold temperatures induce me to save energy as possible.

=P

Regards!

PS: very sympathetic the macro name... just as if I had chosen it