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. 



is there any suggestion for me to move forward in a good ideas?

10 Replies
best response confirmed by syazaaoa95 (Contributor)



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 right.JPG


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?


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




And also about this?

How to do. I'm sorry for asking




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:



This is the formula in cell C3:





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 !



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.



Maybe these formulas return the intended result.




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.