2

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!

1
  • Are the pattern is fix,, right four characters are numbers in all cells? Sep 7, 2018 at 10:35

2 Answers 2

4

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

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

2
  • 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, 2018 at 5:54
  • To remove first three letters ... is enough to use MID(cell, 4, 65535).
    – Akina
    Sep 7, 2018 at 6:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .