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




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

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:
Street Address
City, State ZIP

Other times an entry is:
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!!!



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


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

How will you indicate the End of Record?


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!!

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
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
s2.Cells(i, j).Value = v
j = j + 1
End If
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:

This macro uses Sheet1 and Sheet2 specifically.




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.


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?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question