Spaces in Data

  • Thread starter Thread starter Rhall
  • Start date Start date
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.
123 Main Street


Thanks
RRH
 
Hi,

I think, you might need to clean your database. If the "spaces" are real
spaces (and not other non-printable characters usually resulting from
copy/pasting html sources to Excel) you could create a temporary column and
assuming that cities are in column [A] you could write the following formula
in cell [B1] and copy it down:

=TRIM(A1)

After that, select the column press Ctrl+C (to copy), select the column
[A] go to menu Edit>Paste Special, choose the option 'Values' and press
'OK'. Now you can erase the column .

If the function TRIM doesn't remove the "spaces", then try to use the
formula =CLEAN(A1) instead. This function removes all non-printable
characters from string.

And, of course, you could use both functions together, just in case there is
a combination of spaces and other non-printable characters, like this:
=TRIM(CLEAN(A1))

Regards,
KL
 
Back
Top