Forum Discussion
Find number before and after "x"
- check 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),""),
try this:
=LET(in,C1,
notvals,IFERROR(TEXTSPLIT(in,{",",".","x"," "}, CHAR(SEQUENCE(10, , 48)), 1),CHAR(1)),
vals,TEXTSPLIT(in, notvals,,1),
xVals, SUBSTITUTE(FILTER(vals,ISERROR(--vals),""),"x",""), out,INDEX(FILTER(xVals,NOT(ISERROR(--xVals)),0),1),
out)
note I give credit to davidleal for his answer in another thread making this option much easier
Note 2: i changed it to accommodate a space between the x and the number, and in case an "x" appears before the first x123
Apr 20 2023 01:09 AM
This is much better than the one i am using only issue i have is it does not pick up the number if the X number is only at the end.
E.G
"Add -Multiple-BASE2000 x5" = it comes back with 0 (should be 5)
"Remove User - <User name> - BASE2000 x2" = it comes back with 0 (should be 2)
I can always use my old and this new formula and just do a cross check at end.
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.
- Thanks you very much for your time. think you have got it as it seem to have found all numbers.
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- check 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),""),
