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.
=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.
- Hi Pawn.
Thank you so much for the response.
Meaning that, if I were to use the split on my overall data, meaning that I had to enter min(iferror(search(" ") for each of the different delimiter? If it had any different?You are welcome. For every additional delimiter you can add an IFERROR(SEARCH( to the MIN function. I've highlighted the added part in the formula below.
=LEFT(A3,MIN(IFERROR(SEARCH("IR",A3),9^99),IFERROR(SEARCH("CS",A3),9^99),IFERROR(SEARCH("I5",A3),9^99),IFERROR(SEARCH("ABC",A3),9^99))-2)
OliverScheurich
Hi Pawn,
Look. I've tried for another data.
It seems like it have case sensitive. which it reaches the letters from the left first. Because my data have Original "AL" which it reads from my Search("AL", formula.
How to solve this 😞
