Microsoft Excel - Trim is not working, Unable to remove spaces

Asked By Rajender Prasad on 05-Mar-13 06:31 AM

Dear All,

There is some data which was copied from one excel to other excel. And there were some spaces at prefix of them.
And trim function is not working, and replace is not working, am going mad, and that value should be 10 digits only, if not "0" need to append at prefix .

Ex: Below has 1,3,5 value are having such space.

06430

2641243000

533343000

5443343000

0133343000

6894833000

AS it is copied from other source it has 11 digits including space, and am not able to remove that space.

Give me some formula or code in vba to perform this.

Please help.

Regards,
Prasad

Robbe Morris replied to Rajender Prasad on 05-Mar-13 11:00 AM

Post your code. Either it is wrong or the invisible character you can't see with the naked eye is not a space.

Harry Boughen replied to Rajender Prasad on 05-Mar-13 02:46 PM

Hi Prasad,
First find out what the space is - using CHAR(LEFT(A1,1)). This will give you a number for the ASCII code. Once you know that your should be able to detect and remove it.
Regards
Harry

Rajender Prasad replied to Harry Boughen on 05-Mar-13 11:09 PM

Dear Harry,

Once I run the above mentioned formula, I am getting some symbols as output like below.
Please help now.

#VALUE!

Harry Boughen replied to Rajender Prasad on 06-Mar-13 01:36 AM

Sorry Prasad,
It should have been CODE(LEFT(A1,1)). Also it gives the ANSI code not the ASCII code as I said before.
From the list that you published it appears to be code 160 which is a non-breaking space.
Use this to remove it: =SUBSTITUTE(A1,CHAR(160),"")
If the first formula gives you some other code substitute that value for 160.
Regards
Harry

Rajender Prasad replied to Harry Boughen on 06-Mar-13 09:22 AM

Thanks ton, that is working fine, Thanks a lot..