Drop a prefix with RIGHT and LEN
PremiumEvery order ID starts with a 3-letter channel code, but the numbers have different lengths. In B2, take everything after the first 3 characters with RIGHT, and use LEN(A2)-3 for how many.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Number | ||||
| 2 | WEB10482 | |||||
| 3 | APP981 | |||||
| 4 | WEB2210377 | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock LEFT, RIGHT and MID and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for admin jobs
Why this works
LEN(A2) counts 8 characters in WEB10482. Take away the 3 for the channel code and 5 are left, so RIGHT(A2,5) gives "10482". Filled down, the same formula takes 3 characters from APP981 and 7 from WEB2210377.
Common mistake: Counting the digits by hand and typing RIGHT(A2,5). The formula breaks on the next row, where the number is shorter or longer.
The answer
- B2
=RIGHT(1A2,2LEN(A2)-3)
In plain words: Take 2as many characters as A2 is long, minus 3 from the right end of 1the order ID in A2.