MS Excel Formula

Jageranimus

Hello,

I am currently stuck on a formula in excel, here is an outline of my problem:
I have a column in excel containing strings of data such as:

XXX88888888
XXX11111111
XXX000/00/88888888
XXX000/00/11111111

I need to get rid of "000/00/" in front of the numbers and I am using the following formula to do so, you will notice that i need to add "XXX" in front as well.

My Formula:
=IF(SEARCH("/",D2)<>7,D2,"XXX"&RIGHT(D2,LEN(D2)-SEARCH("/",D2)-3))

The issue is that if SEARCH("/",D2)<>7, then D2 is not displayed, it says #VALUE! instead, but if the false part outputs the correct answer such as XXX88888888

PrzemoF

If the head/tail is fixed lenght you can consider using only left/right functions.

=left(d2,3) & right (D2,8)

It's bulletproof, but the input data has to be 100% consistent.

Jageranimus

Excellent, thanks.
Much simpler solution, ended up with ="XXX"&RIGHT(D2,8)

Jageranimus

And just realized why I went for the long solution in the 1st place, the date is not consistent, some of the entries are smth like 00-888888881111111, they exceed 8 characters, that's the reason for trimming everything before and including "/", data with slashes is consistent, but i need to get rid of the "000/00/" bit.

fionny

I'd just do a find and replace on it personally,

Jageranimus

Got it, thanks for your help, formula at the end is as follows:

=IF(FIND("/",D2&"/",1)=7,"XXX"&RIGHT(D2,8),D2)