Excel string extraction-CodePudding

My question is, I want to find a specific characters "V-", "C-", or "To", from the value in the cell B2, The logic is: if either of these characters are found in the cell B2, the excel should take value from D2, and put into new column say E Here is my if formula:

=IF(MID(B2,FIND("V-",B2),2)="V-",D2,
IF(MID(B2,FIND("C-",B2),2)="C-",D2,
IF(MID(B2,FIND("To",B2),2)="To",D2," ")))

This formula is working for only first occurrence "V-" in this case , the rest of the values it displays "#VALUE!". Can I get any help please.

CodePudding user response：

And return a blank otherwise?

=IF(COUNT(FIND({"V-";"C-";"To"},B2)),D2,"")

CodePudding user response：

Just to spell out what's happening, if it Find fails to find "V-", it immediately returns a #VALUE! error and the formula doesn't proceed any further.

You could fix it like this while preserving your logic

=IF(ISNUMBER(FIND("V-",B2)),D2,
IF(ISNUMBER(FIND("C-",B2)),D2,
IF(ISNUMBER(FIND("To",B2)),D2," ")))

but as you can see from the other answers there are much shorter ways of doing it.

CodePudding user response：

Try this way:

 `=IFERROR(FIND("V-",B2),IFERROR(FIND("C-",B2),IFERROR(FIND("To-",B2)," ")))`

returning a positive number when found or " " when no match.

(I could not test it because I use a localised version of Excel but the logic is fine)