Text in cell except last 3 characters

[Barbara Wiseman]

Hello,
I have a spreadsheet with text in cells in one column, these vary in length
from 8 to 20 characters, and they are a mixture of numbers and alphas.
What I need to do have a formula in another column which takes each of
these, but with out the last 3 characters. There is no one structure to the
cells either, the placement of numbers and alphas is not uniform
I have played with several of the text formulae, but not hit on the answer,
does anyone have any ideas how I could do this.

Example

3H2ERG5HJ123 should be 3H2ERG5HJ in another column
6ND3002TRV should be 6ND3002 in another column

Barbara.

 

[Niek Otten]

Hi Barbara,

=LEFT(A1,LEN(A1)-3)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
| I have a spreadsheet with text in cells in one column, these vary in length
| from 8 to 20 characters, and they are a mixture of numbers and alphas.
| What I need to do have a formula in another column which takes each of
| these, but with out the last 3 characters. There is no one structure to the
| cells either, the placement of numbers and alphas is not uniform
| I have played with several of the text formulae, but not hit on the answer,
| does anyone have any ideas how I could do this.
|
| Example
|
| 3H2ERG5HJ123 should be 3H2ERG5HJ in another column
| 6ND3002TRV should be 6ND3002 in another column
|
| Barbara.
|
|

 

[Guest]

Or: =REPLACE(A7,LEN(A7)-2,4,"")

 

[Barbara Wiseman]

Niek,
Thank you so much, just what I was looking for, and such a quick answer too.
Barbara.