HELP!! trying everything to remove first space in a column of data...nothing working

[clancy1104]

Hi there,
I imported a set of data from word that was comma delimited. I needed it to be a .csv to import it.
The data is all in Column A, about 2000 records. Some have the space, some do not.
I have tried trim, a specific formula, another one...and so on. i have attached an example of the file showing
a. the columns have some spaces, the others do not
b. my trim attempt to the right with to no avail

If i have to manually delete spaces in 2,000 records, i might shove a pen in my eye. Surely i'm missing something, please someone help!
Signed,
spending way too much time on this excel file in Tampa

[Jonmo1]

Perhaps the spaces aren't really spaces
Try the CLEAN function

[benishiryo]

welcome to the forum, clancy1104. Jonmo's right. they arent really your normal spaces. it's called the non-breaking spaces (character 160) vs the normal space (character 32). you can try using:
=SUBSTITUTE(A1,CHAR(160),"")

[oeldere]

Try this one on a dummy of your sheet.

You need to copy to formula in cell a2

Dutch

Please Login or Register  to view this content.

See the attached file.

Notice you need also to copy cell a1 in the sheet data.

[oeldere]

I added the other solutions in your file.

Clean isn't working (see the attached file).

The formula of Benishiryo is perfect.

See the attached file.

[clancy1104]

benishiryo!
amazecakes!
worked...BUT, now i have another challenge. i have to upload into a file transfer that ONLY allows me to upload the data contained in Ccolumn a. Now i have my corrected data in column b, and if i delete column a, it obviously loses the formula info.
Any thoughts there? how can i get the corrected data, without the character to live in column a?
thanks again, you've almost saved my sanity

[oeldere]

I'm not Benishiryo, but maybe I can give you that anwer also.

If not, I will delete it.

copy copy A

paste special in Column B

Copy colum B tot Column A.

(that's all)