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

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




--
A36B58K641

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

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

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

=LEFT(A1,7) 'Remove last 3
=RIGHT(A1,7)'Remove First 3
"Mr. Low" wrote:

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




--
A36B58K641

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

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))



"Mr. Low" wrote:

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




--
A36B58K641

Excel 2002 : How to remove the digits from original reference
 

Hello Mike,

Yes, it works fine.

Many thanks

Low


--
A36B58K641


"Mike" wrote:

=LEFT(A1,7) 'Remove last 3
=RIGHT(A1,7)'Remove First 3
"Mr. Low" wrote:

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




--
A36B58K641

Excel 2002 : How to remove the digits from original reference
 

Hello Dave,

Thank you for the formula.

It works well.

Kind Regars

Low

--
A36B58K641


"DaveO" wrote:

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

Excel 2002 : How to remove the digits from original reference
 

Hello Dave,

Thank you for the formulas

It works well.

Kind Regards

Low




--
A36B58K641


"DaveO" wrote:

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