Forum Discussion
Find number before and after "x"
- Apr 20, 2023check line 5:
out,IFERROR(INDEX(FILTER(vals,NOT(ISERROR(--vals)),0),1),0),
and make it
out,IFERROR(INDEX(FILTER(vals,NOT(ISERROR(--vals)),""),1),""),
actually it isn't about it being at the end it is because you have numbers before AND after the "x" so both of the above come back with "2000 5" and because of that it isn't a valid result.
IF "x 3" is valid but "3 x" is NOT valid then this will work:
=LET(in,A1, tag, "x",
notvals,IFERROR(TEXTSPLIT(in,HSTACK({",","."},tag,tag&" "), CHAR(SEQUENCE(10, , 48)), 1),CHAR(1)),
tagVals,TEXTSPLIT(in, notvals,,1),
vals, SUBSTITUTE(FILTER(tagVals,ISERROR(--tagVals),""),tag,""),
out,IFERROR(INDEX(FILTER(vals,NOT(ISERROR(--vals)),0),1),0),
out)
note, YOU can customize this. The part in line 2: HSTACK({",","."},tag,tag&" ") are all the non-number exception that should be included where tag is defined as "x" in line 1. So in this version I allow numbers (including decimal and commas) next to "x" or after "x" with a space. You can 'play' with this by changing the "out" on line 6 to "tagVals" to see what it finds as 'tagged values in the string' or to "vals" to see which of those 'tagVals' it thinks are potential after removing actual numbers and removing the 'tag' value and then finally 'out' which then returns the 1st found 'vals' that is valid number.
from your notes i am going to try and de-struct this sting to see how it is all work and try and learn from it,
not a big deal as i can do it with a filter and replace.. but if it cannot find a number it come back with 0. is there a way to leave the field blank