How can I add a character to the end of every row in Excel 2000?

[Mostifer]

Hello,

I "inherited" a Word document that has more than 19000 rows when I export it
into Excel. It's an address list that is nearly useless to me in a Word
format.

What I really would like to do is to automatically reformat it so that I can
use it in Excel... however, I'm afraid that it's not possible to do that.

Essentially, I have addresses where sometimes they display with the
following info:
Name
Business
Street Address
City, State ZIP

Other times an entry is:
Name
Title
Business
Street Address 1
Street Address 2
City, State ZIP

Each of the line items above are in their own row when I copy it into Excel.
What I would LOVE is to have each entry in its own individual row with
multiple columns like:

Name Business Address City State ZIP

Or something similar. However, since I wasn't able to figure out how to do
that, I thought I would add a character like an asterisk to the end of each
row, copy it into a Notepad file, then import it into Excel and have it
recognize the asterisks as the delimiter.

BUT, I couldn't figure out how to add an asterisk to the end of every row.
Does anyone know how to do that? Or to do the formatting natively within
Excel so I don't have to add the character then re-import it?

Thank you!!!
Mo

 

[Barb Reinhardt]

Have you considered using a Pivot table to accomplish what you want?

 

[Gary''s Student]

If you import the data into Excel directly, is there a single blank row
between each of the records? Something like:

Joe Smith
128 Maple Ave
Springfield IL

David Jones
12 Main Street
Chicago IL

 

[Sheeloo]

Try to find ^p and replace it with *^p

How will you indicate the End of Record?

 

[Mostifer]

Hi Gary''s Student -

Yes, there is a single blank line - just like you describe when I import it
into Excel. Is there an easy way I can get the data into my desired format
because of that blank line?

Thank you!!
Mo

 

[Gary''s Student]

If we put the original data in Sheet1 and leave Sheet2 blank, then this
little macro:

Sub reformatit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim m As Long, n As Long, i As Long, j As Long
s1.Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
i = 1
j = 1
For m = 1 To n
v = Cells(m, 1).Value
If v = "" Then
j = 1
i = i + 1
Else
s2.Cells(i, j).Value = v
j = j + 1
End If
Next
End Sub


will create:

joe smith 123 maple ave sprintfield IL
mary williams apartment 54 12 main street atlanta ga
james ravenswood 12 carter ave princeton nj


starting from raw data that looks like:

joe smith
123 maple ave
sprintfield IL

mary williams
apartment 54
12 main street
atlanta ga

james ravenswood
12 carter ave
princeton nj

The macro looks for a blank record to start the next output row.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

REMEMBER:
This macro uses Sheet1 and Sheet2 specifically.

 

[Mostifer]

Hi Again GS -

YAY - it worked like a champ! I only had to do some other minor editing to
get the columns to line up correctly which I did manually.

THANK YOU SOO MUCH!!!
Mo
=)

p.s. I have one two more edits that I'd like to do on the sheet. This is my
first time here and I don't know what the proper etiquette is - may I ask you
directly here in this thread or should I open a new post?