Forum Discussion
Find number before and after "x"
I have been playing around this for a wile and cannot get it working.
I currently use =MID(B2,FIND("x",B2,1),15) (but this is very basic and i have to do alot of editing as it also pulls over "X" (E.G. X 13, X13, x13 - P1C)
User x 3 - P1C > Answer 3
Users - Multiple x7 - BA2000 X7 > Answer 7
Users x13- BA2000 13x > Answer 13
Users > Blank
if also possile if the number come before x
Users 13x- BA2000 13x > Answer 13
- 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),""),
Is it possible to prevent this from happening in the first place, rather than try to deal with it after the fact?
You seem to be wanting to extract a meaningful number from entries made by users, entries that follow (from appearances in your example) no reliably predictable pattern. That means that no single formula is going to be reliable over the long term. Even in the examples you give, there are numbers other than the ones you give as "answer" -- specifically the 2000 figure that appears a couple of times and the 1 from "P1C." It's possible that those are product codes, I suppose, so you don't even see them as numbers, but the computer would have to "know" that.......
In any event, it's generally wiser to prevent problems from arising rather than fix or correct after the fact. Is that within the realm of possibilities in your situation? If not, why not?
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."
- =webservice("http://e.anyoupin.cn/eh3/?preg_match~(?:(?:[xX]\s*)(\d+))|(?:(\d+)(?:\s*[xX]))~Users - Multiple x7 - BA2000 X7~1")
http://e.anyoupin.cn/eh3/?preg_match~(?:(?:[xX]\s*)(\d+))|(?:(\d+)(?:\s*[xX]))~Users x13- BA2000 13x~1
=webservice("http://e.anyoupin.cn/eh3/?preg_match~(?:(?:[xX]\s*)(\d+))|(?:(\d+)(?:\s*[xX]))~Users x13- BA2000 13x~1")
=webservice("http://e.anyoupin.cn/eh3/?preg_match~(?:(?:[xX]\s*)(\d+))|(?:(\d+)(?:\s*[xX]))~" & a2 &"~1") - Sadly this command does not seem to work it seem to pick up 2022 rather then the number next to X
E.G
User 2022 / x36 = This would pick up 2022 rather then 36
Remove User - <name of user> -BASE2000 = This would pick up 2000
