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),""),
I thought it may of been a long shot.
I suspect it's entirely doable; Excel is remarkably powerful. I'm going to leave that to some of the others here who are really adept at that kind of text-to-number manipulation.
But I do want to press a bit harder on my first point. You wrote:
sadly we cannot fix this issue of the way the data comes in. As it comes from a customer.
- "A" customer. A single person?
- Or are you saying that each instance comes from a customer, and there are multiple instances, and therefore multiple customers?
- Also significant, in either of the two above scenarios:
- In what form does this data come to you?
- How does it get into Excel in the first place, where it then needs to be processed such that the number adjacent to the X gets extracted?
If the former, it should be easy to request that that person separate the number
--if in Excel--in a cell that uses data validation to ensure a number and only a number is entered
--if not in Excel, somehow separated and distinct from the rest of the accompanying tex
If it's multiple customers, I'd still be curious what the process is by which that "messy data" (a) arrives, and (b) ends up in an Excel spreadsheet. If I were in your shoes, I'd be looking for ways to have it arrive "clean." As the expression goes, "an ounce of prevention is worth a pound of cure."
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
- mackan1 replied to mtarler
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
