How to change the individual row into columns

  • Thread starter Thread starter balachandar
  • Start date Start date
B

balachandar

In Excel If each row looks like the below given format

Michael 20, Eastman Avenue Boston US
David 56, George Patel Road Texas US
Michael 20, Eastman Avenue Boston US
David 56, George Patel Road Texas US
Michael 20, Eastman Avenue Boston US
David 56, George Patel Road Texas US


I want to change it to be the following format (Given below). Is i
possible to do that? If so, what are the steps to be followed? I nee
your help. Please reply this mail. Thanks in Advance.

Michael
20, Eastman Avenue
Boston
US

David
56, George Patel Road,
Texas
US

Michael
20, Eastman Avenue
Boston
US

David
56, George Patel Road,
Texas
US

Michael
20, Eastman Avenue
Boston
US

David
56, George Patel Road,
Texas
US

Regards
Bal
 
One way:

Start a new sheet
put this in A1:A4

#'sheet1'!a1
#'sheet1'!b1
#'sheet1'!c1
#'sheet1'!d1

Leave A5 blank and change Sheet1 to the name of the sheet with your data.

Select A1:A5 and drag down as many rows as you need--you have 200 names/address,
then drag it down through row 1000.

Your list will look like:

#'sheet1'!a1
#'sheet1'!b1
#'sheet1'!c1
#'sheet1'!d1

#'sheet1'!a2
#'sheet1'!b2
#'sheet1'!c2
#'sheet1'!d2

#'sheet1'!a3
#'sheet1'!b3
#'sheet1'!c3
#'sheet1'!d3


No select column A and do:
edit|Replace
what: # (pound sign)
with: = (equal sign)
replace all

The strings will be replaced with formulas that return the values from the other
sheet.

If you want you can convert to values
select the column
edit|copy
edit|paste special|values

===========
But if you're making mailing labels, don't do this. Use excel as your data base
and use MSWord as the application to make the mailing labels.

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.
 
Back
Top