Spaces in Data


R

Rhall

Hi,
I am trying to do a mail merge and the excel data that I am using has a
bunch of spaces in the field that shows the city. So when I list the
City St and Zip the state is about 7 spaces after the city like this.
123 Main Street

Anywhere usa 49000

Any ideas?

Thanks
RRH
 
Ad

Advertisements

B

Bryan Hessey

I guess the space removal feature made that question fall flat.

If you wish to remove the spaces from your data, use =trim(A1) or
=trim(A1&" "&A2&" "&A3)

If you wanted to insert more spaces, then
=A1&" "&A3
should hel
 
R

Rhall

So do you mean to format the whole column with
=trim(g1)

Sorry, I don't know much about this yet.
Ramon
 
M

Mangus Pyke

I am trying to do a mail merge and the excel data that I am using has a
bunch of spaces in the field that shows the city. So when I list the
City St and Zip the state is about 7 spaces after the city like this.
123 Main Street

Anywhere usa 49000


Solution 1:
Select the column, Edit --> Replace, put a space in the top field and
replace all (replaces the spaces with nothing). This will cause a
problem if you have a two-word city, such as Boca Raton.

Solution 2:
Suppose your city is in column C with a header row. Insert a column
after and put this in D2:
=TRIM(C2)

This will trim the white space from the end of the city name. Drag
the forumla down, select column D, press Edit --> Copy, Edit --> Paste
Special, select Value, click OK.

Problem resolved.

MP-
 
B

Bryan Hessey

If your required data is in column G, then you would need to use a ne
column (perhaps insert a new column H and push the other column
over),
then in H1 put
=trim(G1)
and click on cell H1 and drag the small plus sign in the bottom righ
corner of the highlight down the column for as many rows as you hav
data in the G column.

This would then give a 'trimmed' column H for you to use
 
Ad

Advertisements

R

Rhall

Got it :) I had to highlight the whole column and copy it> opened a
new workbook and pasted it in> hit ctrl F to the find hit the space bar
three times and clicked into the replace with> and hit the ok button.
Then I put the data back into the original workbook and did the mail
merge again.
So I guess it was that the field had extra spaces when the data was
created.
Thanks to all that sent me a reply and I look forward to learning more
on this site.
You guys are awesome!
Ramona
 
Ad

Advertisements


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

Top