0

I have a sheet with data , i want to remove last three numbers , but in some cells i have text only so i need a formula that remove last three numbers if cell have, For Ex:

Cell A                     |    Cell B 
---------------------------------------------
my data row 1 in 123       |  my data row 1 in
----------------------------------------------
this is 2nd row data in 345| this is 2nd row data in
----------------------------------------------
3rd row data               | 3rd row data

I tried this code =LEFT(F2,LEN(F2)-3)&"" and its work if cell have numbers but problem is their when column have no numbers in last this formula remove 3 words from text. so i need formula that remove only last 3 numbers if cell have, otherwise print data as it as in next column , hope understand my question thanks.

2

You can check whether last three characters are numbers, and remove them only if yes:

=IF(ISERROR(VALUE(RIGHT(A1,3))),A1,LEFT(A1,LEN(A1)-3))

enter image description here

  • 1
    thanks . its work great , thanks for sharing your knowledge – biz Sep 30 '16 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.