Benches Uncategorized

5

Trim trailing spaces in Numbers '09

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

I have tried to use Trim to delete trailing spaces. Which it does, but then if I copy and paste the value of the cell with the trim formula, I get the original value with the trailing space.

I have a large messy spreadsheet with lots of cells with trailing spaces which makes using reference formulas harder for me since I can't find exact matches (due to the trailing spaces)

How do I copy the string without the trailing space?

5 replies

Sign in or join Beachball Help to reply.

  1. 0

    Badunit

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    B2= abc with a trailing space

    C2 = TRIM(B2)

    If I select cell C2, copy, and paste values to D2, D2 becomes abc without any trailing space. I verified it is only three characters by using the LEN function.

    Are you doing something different than this? Did you verify the result with the LEN function? In other words, how do you know it has trailing spaces?

  2. 0

    nis75p06

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    That is exactly what I'm doing. However, when I click in D2, there is still a space to delete at the end.

    I just used LEN to check the length, still doesn't work.

    A2=HADBAN AL NAZHI

    B2=LEN(A2)=16

    C2=TRIM(A2)

    copy C2 and "paste value" into D2=HADBAN AL NAZHI

    E2=LEN(D2)=16.

  3. 0

    nis75p06

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    The TRIM worked fine on the only table on the first sheet of the file. It's the multiple tables on the 2nd and 3rd sheets of the file that it is not working for.

  4. 0

    Badunit

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    If you copy/pasted that text from A2 into your post, the final character is not a space. I copy/pasted from your post to a table and the last character is code 160 (A0 hex) which looks like a space but is not. TRIM will not remove that.

    Try this

    =SUBSTITUTE(A2,CHAR(160),"")

    If that removes other "spaces" in your string that you want to keep, try this instead

    =TRIM(SUBSTITUTE(A2,CHAR(160)," "))

  5. 0

    nis75p06

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Great! Never occurred to me that the trailing space wasn't a space!

    Worked great, thanks!