Find number before and after "x"

[mackan1]

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

[#2]

@mackan1 

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?

[#3]

@mathetes 

thanks. I thought it may of been a long shot.

I should have said I only need the number next to “X”

sadly we cannot fix this issue of the way the data comes in. As it comes from a customer.

[#4]

@mackan1 

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.

1. "A" customer. A single person?
2. Or are you saying that each instance comes from a customer, and there are multiple instances, and therefore multiple customers?
3. Also significant, in either of the two above scenarios:  
   1. In what form does this data come to you?
   2. 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."

[#5]

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 

[#6]

=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")

[#7]

@mackan1 

A variant:

in B2:

=LET(
  split, TEXTSPLIT(A2,{" ","x","-"}),
  num,   NUMBERVALUE(FILTER(split, split<>"")),
  TAKE(FILTER(num, ISNUMBER(num),""),,1)
)

[#8]

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

[#9]

@mackan1 

=LET(
  split, TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,"x ","x")," ","-"),"-",,TRUE),
  IFNA(NUMBERVALUE(SUBSTITUTE(INDEX(split,,XMATCH("*x*",split,2)),"x","")),"")
)

[#10]

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.

[#11]

thanks this one also work well
1 issue is it cannot find the X number in the below. I am guess cos of the ( )
"B2K ID Deletion (x2 ID)" = came back with #VALUE! (should be 2)

each report is around 8k in rows. but even if this cannot be fixed with ( ) then anyhow this would save me a lot of time.

[#12]

@mackan1 

- Could you supply a list of ALL your variations please?

- With 8k rows/report a Power Query approach would probably be more efficient, assuming you run Excel >/= 2016 / Windows?

[#13]

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. 

[#14]

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

[#15 · Accepted solution]

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),""),

[#16]

that is prefect. you are truly a life saver.