Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Critical - Converting Single Column Address Data into Multiple Col

I have a list of addresses in the following order:

names (a1), business names (a2), streets (a3), city state and zip codes
(a4), business phone (a5), cell phone (a6), and two blank lines below each
address listing.

I need to convert each cell of data into columns so I can create a mail merge.

Help is greatly appreciated
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Critical - Converting Single Column Address Data into Multiple Col

You can copy all the data and paste it into Word 2003 and then use the Table
to Text conversion.
Fiddle around with the options until it come out right.

Word 2007 has me stumped.

Z.




"A.J." a écrit dans le message de news:
...
I have a list of addresses in the following order:

names (a1), business names (a2), streets (a3), city state and zip codes
(a4), business phone (a5), cell phone (a6), and two blank lines below each
address listing.

I need to convert each cell of data into columns so I can create a mail
merge.

Help is greatly appreciated



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Critical - Converting Single Column Address Data into Multiple Col

You will need Excel and Word for this.


Your data is all in Column A.
Go to the last not empty cell in A
This is how to do it :
You go to cell B1
Hit Ctrl + Down arrow, that getsyou to the bottom of B.
Move one cell left.
That's the bottom of Column A.
Now hit Ctrl + Up Arrow
Now you're on the last not empty cell of A
Now move one cell right
Key in a zero to this cell
Now hit Ctrl + Up arrow
Now you're back to B1

In B1, key in this formula if(A1="";"XYZ";A1)

That means
if the cell on the left is empty write "XYZ"
otherwise just copy the contents of the cell on the left.

Now you have to copy this formula all the way down to the zero that we left
sitting there in B.
Easy. Don't use the mouse.
With the cursor still in B1
Press Shift + Ctrl + Down arrow.
This will select all the way down to the zero.
Now relase the keys and then
Press Ctrl + D.
This will Copy the formula all the way down.

Release the keys.
Now press Ctrl + C to copy all that into the Clipboard.
if you are in XL 2003 --- Now take the mouse and go to the menu, choose
Edit, paste Special ,
Click on Values, then OK
if you are in XL 2007 --- Now take the mouse and go to the Home menu,
click on the little arrow under the
word paste Paste, the Paste Values

Now the Column B contains all that was in Column A plus "XYZ" where A has
empty cells.
No formula, just data.
Reselect all the data in B
Then Ctrl + C to copy it all into the clipboard.


Open Word.

Gto the menu , edit, paste as plain text
You should have the column of data.
Now press the little Icon with the paragraph character that lets you see the
hidden characters that Word uses.
You should see a paragraph character at the end of each line.
That's OK.

Now, we are going to replace each one onf these paragraph character into a
tabulation.
In Word language this "paragraph character " is written "^p", without the
quotes.
In Word language this "tabulation character " is written "^t", without the
quotes.


press Ctrl + H.
Depending on what version of Word you're using the Dialogue box is
different.

Find what ? Key in "^p" without the quotes.
Replace with ? Key in "^t" without the quotes.

Or you can use the More Button, then the Special button,
to Find the "paragraph character" option and then again
to Replace with the tabulation character.


When you click OK Word will do the replace.
This may take some time.
Your text will look crazy and useless but don't worry.
When it's finished, save the document.
This will free up dome memory.

Press Ctrl + H again.
Now Find the occurences of XYZ and
replace them with ....... the paragraph character !!

Now the text should look a lot better.
Save the document as plain text.
Open it with Excel.
Happy ?
Merge.


HTH
Zumble.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting a matrix of data into a single column Hosley Excel Discussion (Misc queries) 6 April 19th 07 06:07 AM
stacking multiple column data into a single column Chubby Excel Worksheet Functions 1 January 12th 07 08:24 AM
Converting an array of data into a single column Raj Excel Discussion (Misc queries) 0 August 15th 06 09:21 PM
merge data from multiple columns to single column triggerthehorse Excel Worksheet Functions 2 January 17th 05 08:19 PM
how to convert multiple columns of data into one single column? Ah 3 Excel Worksheet Functions 1 November 12th 04 03:33 PM


All times are GMT +1. The time now is 11:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"