Forum Discussion
Can't I make the formula so that it will read in order?
Hi,
I have another problem. which my problem is about, I want it to read on my first argument on the front, and after that it will read the arguments after that if the argument on the front does not satisfied. besides, if all is not satisfied, I want the next thing happen is it will be blank. which it returns blank on the cell.
Is there any other way for me to get this things straight.
Thank yo so much!
syazaaoa95 Perhaps not too complex after all.
Begin by listing of all possible delimiters and create a named range of it. In the example I called it "delim". Then the following formulas will create the LEFT and RIGHT segments.
=TRIM(IFERROR(LEFT([@DESCRIPTION],MIN(IFERROR(FIND(delim,[@DESCRIPTION])-1,999999))),[@DESCRIPTION]))
=TRIM(IFERROR(RIGHT([@DESCRIPTION],LEN([@DESCRIPTION])-MIN(IFERROR(FIND(delim,[@DESCRIPTION])-1,999999))),""))
See attached.
9 Replies
Does this return the intended result?
It helps on the blank one. but for the other conditions, I still can't get the result.
I think my data is not good enough for my intended result. I'll attached add some more there in the excel.
Maybe this is too complex. For example what would be the intended result for this description: Original Sharp Toner Sensor Chip AR-016FT SF1016 SF1018 SF1020
In C2:
=IFERROR(RIGHT(A2,LEN(A2)-(MIN(IFERROR(SEARCH("MXM",A2),9^99),IFERROR(SEARCH("MX7",A2),9^99),IFERROR(SEARCH("MX7",A2),9^99),IFERROR(SEARCH("MX1",A2),9^99),IFERROR(SEARCH("MX2",A2),9^99),IFERROR(SEARCH("MX4",A2),9^99),IFERROR(SEARCH("MX3",A2),9^99),IFERROR(SEARCH("DX",A2),9^99),IFERROR(SEARCH("MX5",A2),9^99),IFERROR(SEARCH("MX6",A2),9^99),IFERROR(SEARCH("MXB",A2),9^99),IFERROR(SEARCH(" AL",A2),9^99)+1,IFERROR(SEARCH("ARM",A2),9^99),IFERROR(SEARCH(" ARM",A2),9^99)+1,IFERROR(FIND(")",A2),9^99)+2,IFERROR(SEARCH("BP",A2),9^99),IFERROR(SEARCH("MXC",A2),9^99),IFERROR(SEARCH(" SF",A2),IFERROR(FIND("AR",A2),9^99)+2)-2)-1)),"")
Fill down.
- Hi Hans,
Thank you so much for giving me suggestion on how to make it in blank state.
