Excel 2002 : Why =Trim () formula not working ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear Sir,

I copied a list of reference number in HTML format from the MS Outlook and
paste it to Excel worksheet. After that I separated it into columns by using
Data > Text t to Columns function.

A B
1 BJH3675
2 BGB1056
3 BHH9444
4 BJH978
5 BFU7095
6 BFU3422
7 BFG9144
8 BFN3659
9 BFN3756


After separation the data in B is in text format.

I find that all the reference number in column B has 4 leading spaces.

I tried to remove these empty spacing by using =TRIM( ) or =TRIM(CLEAN( )),
but it does not work .

May I know why this could happen and how to overcome this problem >

Thanks

Low
 
Press Ctrl-H
Click in the Find What line , then hold down the Alt key and use the numeric
keypad to enter 0160.
Make sure the Replace With box is empty
Click Replace All
 
You can check what the spurious characters are by =CODE(LEFT(A1))
Other contributors have suggested a replacement of CHAR(160), but the CODE
function will let you see whether it is 160, and if not what it is.
 
Hello Duke,

Many thanks for your special formula to solve the problem.

It works out well.

Low
 
Hello Toppers,

Many thanks for your special formula to solve the problem.

It works out well.

Low
 
Back
Top