Hi,
So basically I have 4 main possibilities:
PYMT769291
INV 769291
INV 769291_1 -> This could be _2 or _3 or _4...
INV 769291BD
And I need 1 formula that makes them all convert into this:
PYMT769291 -> This one should never change
769291 -> This one gets rid of the "INV "
769291 -> This one gets rid of the "INV " & everything after "_"
769291 -> This one gets rid of the "INV " & the 2 letters at the end
So far I have this:
=IFERROR(LEFT(SUBSTITUTE(A1,"INV ",""),FIND("_",SUBSTITUTE(A1,"INV ",""))-1),SUBSTITUTE(A1,"INV ",""))
And I get the following:
PYMT769291
769291
769291
769291BD
I do not know what formula to use to delete the letters at the end of the last one "without" changing the other 3 which are already as I want them to show. Basically a formula that will only delete letters on the right up to the first number it finds, and if it doesn't find any letters, then it gives back the original. Any ideas? I'm a novice, so apologies if my question is too easy for you hahah.
Thank you very much in advance!
Cristian
So basically I have 4 main possibilities:
PYMT769291
INV 769291
INV 769291_1 -> This could be _2 or _3 or _4...
INV 769291BD
And I need 1 formula that makes them all convert into this:
PYMT769291 -> This one should never change
769291 -> This one gets rid of the "INV "
769291 -> This one gets rid of the "INV " & everything after "_"
769291 -> This one gets rid of the "INV " & the 2 letters at the end
So far I have this:
=IFERROR(LEFT(SUBSTITUTE(A1,"INV ",""),FIND("_",SUBSTITUTE(A1,"INV ",""))-1),SUBSTITUTE(A1,"INV ",""))
And I get the following:
PYMT769291
769291
769291
769291BD
I do not know what formula to use to delete the letters at the end of the last one "without" changing the other 3 which are already as I want them to show. Basically a formula that will only delete letters on the right up to the first number it finds, and if it doesn't find any letters, then it gives back the original. Any ideas? I'm a novice, so apologies if my question is too easy for you hahah.
Thank you very much in advance!
Cristian