Excel 2002 : How to remove the digits from original reference ?

[Guest]

Dear Sir,

May I know what formula I must input at cell B1 to remove the last 3 and the
first 3 digits of the reference number in column A as illustrated?


1. To remove last 3 digits

A B
Before After
0410003003 0410003
0411923001 0411923
0411924002 0411924


2. To remove first 3 digits

A B
Before After
0409596001 9596001
0409596002 9596002
0409596003 9596003


Thanks

Low

 

[DaveO]

To remove the last three digits, assuming your "before" entry is in
cell A1:
=MID(A1,1,LEN(A1)-3)

To remove the first three digits, assuming your "before" entry is in
cell A2:
=MID(A2,4,LEN(A2))


Dave O

 

[Guest]

=LEFT(A1,7) 'Remove last 3
=RIGHT(A1,7)'Remove First 3

 

[Guest]

Hi Mr. Low,
To remove the first and the last 3 digits of the number in a cell eg. cell
A2, use this formula:

=LEFT(RIGHT(A2;(LEN(A2)-3));(LEN(A2)-6))

 

[Guest]

Hello Mike,

Yes, it works fine.

Many thanks

Low

 

[Guest]

Hello Dave,

Thank you for the formula.

It works well.

Kind Regars

Low

 

[Guest]

Hello Dave,

Thank you for the formulas

It works well.

Kind Regards

Low