Forum Discussion
Trying to figure out the problem
=LEFT(A3,MIN(IFERROR(SEARCH("IR",A3),9^99),IFERROR(SEARCH("CS",A3),9^99),IFERROR(SEARCH("I5",A3),9^99))-2)=RIGHT(A3,LEN(A3)-(MIN(IFERROR(SEARCH("IR",A3),9^99),IFERROR(SEARCH("CS",A3),9^99),IFERROR(SEARCH("I5",A3),9^99))-2)-1)You can try these formulas. The first formula is in cell B3 and filled across range B3:B5 and the second formula is in cell B7 and filled across range B7:B9. Both formulas check which delimiter occurs first in a string. Then this is used to split the text into two results.
I should have used FIND instead of SEARCH in order to perform an exact match including small or capital letters.
This is the formula in cell B3:
=LEFT(A3,MIN(IFERROR(FIND("IR",A3),9^99),IFERROR(FIND("CS",A3),9^99),IFERROR(FIND("I5",A3),9^99),IFERROR(FIND("ABC",A3),9^99),IFERROR(FIND("AL",A3),9^99),IFERROR(FIND("SF",A3),9^99))-2)
This is the formula in cell C3:
=RIGHT(A3,LEN(A3)-(MIN(IFERROR(FIND("IR",A3),9^99),IFERROR(FIND("CS",A3),9^99),IFERROR(FIND("I5",A3),9^99),IFERROR(FIND("ABC",A3),9^99),IFERROR(FIND("AL",A3),9^99),IFERROR(FIND("SF",A3),9^99))-2)-1)
OliverScheurich
Thank you so much for bearing with me.
Now I have my problem with the zero value.
I'm trying to add up with IFERROR(SEARCH(" ",BLANK)
I want to return the output to be blank, so that which of the description that does not have model, it will be return in blank.
so that it won't return #value.
thank you Pawn and sorry for asking too much !
I saw your screenshot after i had already answered. I think this can be solved by wrapping the formula into IFERROR. I've highlighted the IFERROR in the sample formula.
=IFERROR(RIGHT(A3,LEN(A3)-(MIN(IFERROR(FIND("IR",A3),9^99),IFERROR(FIND("CS",A3),9^99),IFERROR(FIND("I5",A3),9^99),IFERROR(FIND("ABC",A3),9^99),IFERROR(FIND("AL",A3),9^99),IFERROR(FIND("SF",A3),9^99))-2)-1),"")
Do you want a blank cell if none of the values is found in a cell? For example if none of these values {"IR"."CS"."I5"."ABC"."AL"."SF"} is found in cell A3.
=IFERROR(LEFT(A3,IF(SUMPRODUCT(N(ISNUMBER(FIND(({"IR","CS","I5","ABC","AL","SF"}),A3))))=0,"",MIN(IFERROR(FIND("IR",A3),9^99),IFERROR(FIND("CS",A3),9^99),IFERROR(FIND("I5",A3),9^99),IFERROR(FIND("ABC",A3),9^99),IFERROR(FIND("AL",A3),9^99),IFERROR(FIND("SF",A3),9^99))-2)),"")=IF(B3="","",RIGHT(A3,LEN(A3)-(MIN(IFERROR(FIND("IR",A3),9^99),IFERROR(FIND("CS",A3),9^99),IFERROR(FIND("I5",A3),9^99),IFERROR(FIND("ABC",A3),9^99),IFERROR(FIND("AL",A3),9^99),IFERROR(FIND("SF",A3),9^99))-2)-1))Maybe these formulas return the intended result.