May 13 2023 10:39 PM
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. :downcast_face_with_sweat:
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?
May 14 2023 01:22 AM
Solution=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.
May 14 2023 04:57 AM
May 14 2023 07:15 AM
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)
May 14 2023 06:46 PM
May 14 2023 07:53 PM
@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 :(
May 14 2023 09:07 PM
May 15 2023 01:03 AM
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)
May 15 2023 01:37 AM - edited May 15 2023 01:52 AM
@OliverScheurich
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 !
May 15 2023 02:03 AM
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.
May 15 2023 02:08 AM
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),"")
May 14 2023 01:22 AM
Solution=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.