Unwanted Spaces (I think)

[excelnovice19]

Please see the attached file. Simple copy/paste of names into Column A and then text-to-columns to Column B&C; Finally combining names in Column D. After the last name, there is unwanted spaces (I can only assume that's what it is). I've tried TRIM with no luck; F5, Special, Blanks and no luck; Find and Replace with no luck. I cannot get rid of these gaps. Any suggestions? Thanks.

[shg]

They are non-breaking spaces, CHAR(160).

To remove, do find and replace. In the Find box, press and hold the Alt key, then press 0160 on the NUMERIC keypad. Leave the Replace box blank, press Replace All.

[sandy666]

try replace char(160) with ""

[excelnovice19]

Was able to get "shg" to work but not "sandy666." For future, how can I tell an Excel CHAR code/function? Thanks.

[sandy666]

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

[excelnovice19]

Works perfectly. Missed Substitute. Thanks. Lastly though, how could you tell CHAR was my initial obstacle?

[sandy666]

excess spaces [TRIM() ], non-printable characters [CLEAN() ], and CHAR(160)
together: =CLEAN(TRIM(SUBSTITUTE(A2,CHAR(160),"")))
popular problem

[TMS]

Lastly though, how could you tell CHAR was my initial obstacle?

It's a common problem when data comes from external applications, particularly data downloaded from the Internet.

[shg]

how could you tell CHAR was my initial obstacle?

I use an add-in form. See https://app.box.com/s/gfo2l90kenhcnj3vjjx1bi0qpy6m85g7; it includes a ReadMe with instructions to install.

[sandy666]

If the basic problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks

[Pete_UK]

... Lastly though, how could you tell CHAR was my initial obstacle ...

You can put this formula in E2:

=CODE(RIGHT(C2))

then copy down. You can see that all the names in column C end with the character 160.

Hope this helps.

Pete