1

I have an extremely basic understanding of excel so please cater your answers accordingly! I need to subtract one cell from another, and then add one to the result, however the I need the formula to ignore the first three characters in the cell (both numbers and letters).

For Example -

A1 Contains - X2X1234 B1 Contains - X7X1256

I want cell C1 to contain contain 23. [(1256-1234)+1]

The cells will always be in the format of letter number letter, and then the four numbers I actually care about. X1XYYYY

Thanks for the help!

  • Are the pattern is fix,, right four characters are numbers in all cells? – Rajesh S Sep 7 '18 at 10:35
3

Use RIGHT function to return the last character or characters in a text string, based on the number of characters you specify.

Syntax

RIGHT(text,[num_chars])

  • Text Required. The text string containing the characters you want to extract.

  • Num_chars Optional. Specifies the number of characters you want RIGHT to extract.

Formula to use in C1

=RIGHT(B1,4)-RIGHT(A1,4)+1

enter image description here

| improve this answer | |
  • 2
    The author claims the data format is X1XYYYY, so 2nd arg of Right fn in a formula must be 4. – Akina Sep 7 '18 at 5:13
  • 1
    @Akina lol whoops, thanks for pointing that out, I have fixed the answer to reflect :) – angelofdev Sep 7 '18 at 5:16
1

To remove first three letters from the string use this formula

=RIGHT(Cell_No,LEN(Cell_No)-3) For example: =RIGHT(B6,LEN(B6)-3) 

Now you got the digits, Do the calculation as you wish

| improve this answer | |
  • Hi, you seem to have just shown how to remove the first 3 characters of a specific cell by using the LEN function. You should just update your answer to give the OP the whole formula =RIGHT(B1,LEN(B1)-3)-RIGHT(A1,LEN(A1)-3)+1 – angelofdev Sep 7 '18 at 5:54
  • To remove first three letters ... is enough to use MID(cell, 4, 65535). – Akina Sep 7 '18 at 6:12

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.