I need help to write a formula that will remove the first 2 digits of a number, but only if it's 19.
Example: 191234-5678 would need to become 1234-5678. Example2: 181234-5678 would remain the same 181234-5678
I've tried a few left/right and trying to add an if but im not knowledgeable enough to make it work
I need help to write a formula that will remove the first 2 digits of a number, but only if it's 19.
Example: 191234-5678 would need to become 1234-5678. Example2: 181234-5678 would remain the same 181234-5678
I've tried a few left/right and trying to add an if but im not knowledgeable enough to make it work
Share Improve this question edited Mar 19 at 11:19 Mayukh Bhattacharya 27.8k9 gold badges29 silver badges42 bronze badges asked Mar 19 at 11:16 user29990582user29990582 13 Answers
Reset to default 3Try using the following formula:
=REPLACE(A1,1,(--LEFT(A1,2)=19)*2,)
Try this formula:
=IF(LEFT(A1, 2)="19", RIGHT(A1, LEN(A1)-2), A1)
Another solution using REGEXREPLACE
=REGEXREPLACE(A1,"^19","")
Or REGEXEXTRACT
=REGEXEXTRACT(A1,"^(?:19)?(.*)",2)