Trim, clean and substitute function do not work for these data

**radzian** · 08-02-2008, 05:30 AM

Hi ....

I have tried to trim, clean and substitute data. However, it seems that the function does not work on these data.

I attached the samples data in this thread. The data to be cleaned is in column A

Advice and help is very much appreciated. Thanks.

**VBA Noob** · 08-02-2008, 05:38 AM

Try

=LEFT(A2,LEN(A2)-1)

Pastespecial values to convert to numbers

VBA Noob

**radzian** · 08-02-2008, 05:48 AM

VBA Noob,

Thanks, great , it works .

What LEFT function do to these data ? .. to understand my data problem.

**VBA Noob** · 08-02-2008, 05:53 AM

The left function combine with len function returns all cells to the left of the number of characters -1 e.g ignore the last character

I tried again. By copying the blank character at the end of A2 and adding to the formula below it worked ok

=SUBSTITUTE(A2,"*","")

Edit. The blank at the end appears to be a * not a blank which is why your formula didn't work
or try

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

See attachment (Please save attachments next time as xl2000)

VBA Noob

**radzian** · 08-02-2008, 07:46 AM

VBANoob

Thank you. That is fast help.

I have been figuring out the problem including , what character it is for this one day... but you solve it only in 5 minutes .

I really appreciate your help.