Hello Lokesh,
If you provide a sample file your requirement will be clearer. Are you
importing a number of addresses from a text file?
If it is just one address you might like the option of simply using the &
concatenation operator or the concatenate function (I always use &) like so:
=A1&", "&A2
the above will work for name in A1 and adderss in A2. You can also add more
cells the same way (. &", "&A3&", "&A4 .) but in your example, the city and
state/province is already included in line 2 (address).
If the field names are in the text ("Name:") you could specify ":" as a
delimiter when importing the text for Excel to automatically use two
columns, or use the Text To Columns feature in Excel to split it post
import, or use FIND() in your formua.
Actually, I just copy-pasted your multiline data into Excel and discovered
(using the CODE and MID functions to examine characters for their numerical
ASCII value) that the colon is followed by a varying number of blanks but
those blanks are not the ordinary space character (which is ASCII code 32),
but code 160 (non-breaking space). There is one final regular space (code
32) just before the actual data, so here is my revised formula that uses
FIND and MID to extract the significant data from the first two lines and
concatenates it. Assumes the multiline data starts in A1:
=MID(A1,FIND(" ",A1)+1,999)&", "&MID(A2,FIND(" ",A2)+1,999)
For importing multiple record of this kind of data, you might be interested
in these tips I posted a while back on a forum:
For a manual/formula approach, check out this book excerpt from MrExcel. He
steps through it starting on page 465, and it looks like it is pretty much a
ready made solution for you:
http://www.mrexcel.com/manyrows.pdf
For a VBA approach, here are links to routines that read a text file and
import it. They do not specifically handle multi-line records, so you would
have to make a couple modifications that I think would be minor for it to do
so.
http://www.cpearson.com/excel/ImpText.aspx
http://www.cpearson.com/excel/ImportBigFiles.aspx
Here's A multi-line routine, but for data formatted differently than yours:
http://www.mrexcel.com/forum/showthread.php?t=568267
Here's a program for doing these kinds of imports, not free. $19 for a
one-day license, then works it's way up to $149 for an unlimited license:
http://www.beside.com/sample1.html
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Lokesh Loki
Sent: Sunday, March 11, 2012 7:41 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Add business address in a single row
Hi Experts,
I need to add these business addresses in a single row, so could you please
provide me a formula so that I can do further needful action.
Example :- Shapers Hair Salon & Spa, 27- 565 Bernard Ave., Kelowna, British
Columbia, V1Y 8R4, Kelowna, NULL
Name: Shapers Hair Salon & Spa
Address: 27- 565 Bernard Ave., Kelowna, British Columbia, V1Y 8R4
City: Kelowna
State: NULL
Regards
Lokesh.M
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
