Forum Discussion
Can't I make the formula so that it will read in order?
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.
Original Sharp Toner Sensor Chip AR-016FT
RIGHT
SF1016 SF1018 SF1020
That's why I told my boss, that this kind of data is too complex. 😞
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.
- Hi Riny!
It does help to solve it!
I'm wondering because Pawn is using 9^99 and you are using 1,999999 in your formula.
My I know what does it means?
Thank you !!!syazaaoa95 I'm using 9999999, not 1.99999etc
It's just a very big number, as is 99^99, that is given to each 'delimiter position' that is NOT found. Then MIN will take the lowest value in the array to determine the first occurrence of any of the possible delimiters.
Check it out for yourself by dissecting the formula. First just the FIND part, then ADD the IFERROR, then the MIN. Now you are left with a single number that you can feed into either LEFT or RIGHT.