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.
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 😞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)
- Thank you so much Pawn. It does help me a lot!!!
if I got any question, can I just directly ask you? or is it okay for you?