Parse multiple text lines into 1 line in excel

[debbie]

help. I am an excel beginner and can't find out how to turn multipl
lines of text into 1 row in excel. It's probably really easy but m
manual is USELESS. Can anyone help

 

[Gord Dibben]

debbie

You're a little short on details. If nothing below fits the bill post back.

"Multiple lines" is how many and is each line in a separate cell down one
column?

Do you want all lines to go into one cell?

You can use this formula in, say B1, if lines are as above.

=A1 & " " & A2 & " " & A3 & etc. etc.

The " " gives a space between each line.

Do you want all lines in separate cells across the row?

Copy the cells down the column then select a cell out of that range and Paste
Special>Transpose.

There are VBA code solutions also but perhaps as a beginner you don't want to
get into that now.

Gord Dibben XL2002

 

[debbie]

thank you so much for answering.

I have a word document (which i can convert to text), which has
multiple lines for each entry. eg

fred bloggs
10 downing street
westminster
london
0207 6767 6767

john doe
railway terraces
east sheen
richmond
0208 5353 5353

etc etc. I want to put the file into excel so that each persons
details are in one excel row. I could do this manually just for the
above, but the file is big.
If I open the text file in excel, I end up with as many rows as you see
above. I tried to use "text inport wizard", which is great but doesn't
seem to cater for merging multiple input lines into one output line.

 

[Dave Peterson]

The bad news is this is more complex than you would guess.

You'll have to answer some questions to get better replies:

1. How is your data arranged?

Is it always 5 lines (name/address/city/country/mail code)
Or could the number of lines vary between people?

If it does vary, how do you know which group you're on--is there an empty cell
between each group?

If you only have few entries, you may be able to do it manually by selecting
each group, edit|copy, select a cell in column B and edit|pastespecial,
Transpose).

(But if you have a long list (and a nice pattern), I wouldn't do it manually.)

 

[Max]

If, as Dave P. says, your data is nicely grouped,
with each group in 5 lines (as indicated in your post)
this might be worth a quick try on a *back-up* copy of your file

Assume your data is in col A, row2 down (with col label in A1)

Let's take a sample set below in A1:A12

Customer
fred bloggs
10 downing street
westminster
london
0207 6767 6767

john doe
railway terraces
east sheen
richmond
0208 5353 5353
-------------------
Select col A

Press F5 > Special > Check "Blanks" > OK
(this will select all the blank cells in col A)

Right-click (on any selected part) > Delete > Check "Entire Rows" > OK

The sample set will now be in A1:A11
(with the blank row deleted)

Customer
fred bloggs
10 downing street
westminster
london
0207 6767 6767
john doe
railway terraces
east sheen
richmond
0208 5353 5353
---------------------
Put in B2: =INDIRECT("A"&5*ROW()-8+COLUMN()-2)

Copy B2 across to F2
(this gives you the first group)

Select B2:F2 and copy down until zeros appear
(2nd group will appear in B3:F3,
3rd group in B4:F4, and so on - the format you want)

If needed, freeze the values in cols B to F
---------------------
Select cols B to F
Right-click > Copy
Right-click > Paste Special > Check "Values" > OK

Delete col A (if desired)
--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email

 

[Gord Dibben]

debbie

If all the data is as your example, i.e. 5 rows and a blank row, the code
below will move each to 5 columns per row.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 6 to account for the blank rows.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben XL2002

 

[debbie]

Max, you are a god

That's just so neat.
(i wish I'd thought of it)

And it works a treat.

Many thanks. Debbi

 

[Max]

You're welcome, Debbie!
Glad it worked for you.
Thanks for feedback.

cheers
Max