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


--
A36B58K641

try:

in column C:

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

"Mr. Low" wrote:

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


--
A36B58K641

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

"Mr. Low" wrote:

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


--
A36B58K641

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.
--
David Biddulph

"Mr. Low" wrote in message
...
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


--
A36B58K641

Hello Duke,

Many thanks for your special formula to solve the problem.

It works out well.

Low

--
A36B58K641


"Duke Carey" wrote:

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

"Mr. Low" wrote:

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


--
A36B58K641

Hello Toppers,

Many thanks for your special formula to solve the problem.

It works out well.

Low


--
A36B58K641


"Toppers" wrote:

try:

in column C:

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

"Mr. Low" wrote:

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


--
A36B58K641

Hello David,

Thank you for the special formula for error testing.

Best Regards

Low


--
A36B58K641


"David Biddulph" wrote:

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.
--
David Biddulph

"Mr. Low" wrote in message
...
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


--
A36B58K641