Trim trailing spaces in Numbers '09

Question

Level 1

9 points

Trim trailing spaces in Numbers '09

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?

MacBook, Mac OS X (10.6.2)

Posted on Mar 12, 2010 12:49 PM

Reply

Answer 1

Badunit

Level 7

31,351 points

Jan 5, 2011 12:39 PM in response to nis75p06

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?

Reply

Answer 2

nis75p06 Author

Level 1

9 points

Jan 5, 2011 12:40 PM in response to Badunit

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.

Reply

Answer 3

nis75p06 Author

Level 1

9 points

Jan 5, 2011 12:40 PM in response to nis75p06

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.

Reply

Answer 4

Badunit

Level 7

31,351 points

Jan 5, 2011 12:40 PM in response to nis75p06

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)," "))

Reply

Answer 5

nis75p06 Author

Level 1

9 points

Jan 5, 2011 12:40 PM in response to Badunit

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

Worked great, thanks!

Reply