ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

Main Area and Open Discussion > General Software Discussion

Excel formula help?

(1/2) > >>

m_s:
I have a list of people's names and web addresses in Excel.  I am trying to make a Word mail-merge doc that sucks this info from Excel.  In the Excel sheet, some people have just one web address to their name, but others have 5 or 6, each of which is currently listed on a separate line.  I would like a formula that will combine all the pages belonging to each person, including a line break after each address, so that it's all clear and formatted when Word gets hold of it.  Es possible?  Any guidance much appreciated!

katykaty:
Hmmm, Excel doesn't like records being spread over more than one row. Can't think of an easy way off the top of my head.

How tricky would it be to create say 5 extra columns for any extra websites and move the extra websites into there, so there's a single row for each person?

Curt:
http://www.officeletter.com/

kfitting:
I don't know if this will help you or not... check out this website

http://www.officearticles.com/excel/carriage_return_within_a_cell_in_microsoft_excel.htm

Read all three ways and note the pitfalls of each!

Also, there is the formula CONCATENATE which combines pieces of text.  char(13), char(12) are carriage return and formfeed, char(10) is linefeed.

Kevin

Perry Mowbray:
As KatyKaty says Excel wont cope well with your record spanning more than one row.

What you should do is VBA it into a single Cell.

Alternatively, you could
1. Write a formula (a LookUp probably) to concatenate the other cells into a single cell, then
2. Copy and paste the concatenated cells as values
3. Delete the duplicate lines

If you want to send me the file I don't mind having a look at it for you.

Perry