SOLVED

Contributor

# 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?

10 Replies
best response confirmed by syazaaoa95 (Contributor)
Solution

# Re: 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.

# Re: Trying to figure out the problem

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?

# Re: Trying to figure out the problem

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)

# Re: Trying to figure out the problem

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?

# Re: Trying to figure out the problem

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

# Re: Trying to figure out the problem

@syazaaoa95

How to do. I'm sorry for asking

# Re: Trying to figure out the problem

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)

# Re: Trying to figure out the problem

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 !

# Re: Trying to figure out the problem

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.

# Re: Trying to figure out the problem

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),"")