+ Reply to Thread
Results 1 to 6 of 6

Spaces in Data

  1. #1
    Registered User
    Join Date
    07-26-2005
    Posts
    3

    Spaces in Data

    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
    Last edited by Rhall; 07-26-2005 at 10:12 PM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    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 help

  3. #3
    Registered User
    Join Date
    07-26-2005
    Posts
    3
    So do you mean to format the whole column with
    =trim(g1)

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

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    If your required data is in column G, then you would need to use a new column (perhaps insert a new column H and push the other columns over),
    then in H1 put
    =trim(G1)
    and click on cell H1 and drag the small plus sign in the bottom right corner of the highlight down the column for as many rows as you have data in the G column.

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

  5. #5
    Mangus Pyke
    Guest

    Re: Spaces in Data

    On Tue, 26 Jul 2005 20:59:13 -0500, Rhall wrote:
    >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-
    --
    "Learning is a behavior that results from consequences."
    B.F. Skinner

  6. #6
    Registered User
    Join Date
    07-26-2005
    Posts
    3
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1