# Excel Formula to subtract cells from one and other whilst ignoring text

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

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
`````` • 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
• @Akina lol whoops, thanks for pointing that out, I have fixed the answer to reflect :) – angelofdev Sep 7 '18 at 5:16

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

• 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