Forum Discussion
Trying to figure out the problem
Based on my previous question (discussion on the topic How To Split My Data….) I wanted to follow the instructions of a few suggestions given in the comment.
but I end up, getting error.
why? Because I don’t have the TEXTSPLIT function in my excel.
it’s a bit difficult for me to continue on achieving my goals. 😓
Text to columns only can be split my sentences if there are the same delimiters or parantheses (same all the way down of all the sentences).
but due to my each data have not have consistent for me split the text, so it becames difficult to continue using the Text to Columns.
so the suggestion given from my fellow friends that comment on my questions is the function cannot be used on my Excel.
such as TEXTSPLIT / SPLIT
is there any suggestion for me to move forward in a good ideas?
=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.
10 Replies
=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)
