How do I create an extraction Formula to isolate a numeric customer ID?-CodePudding

I have these two formulas isolating the customer ID and [Humcrypt], but I just want the customer ID, I can create a new column and get the numeric value isolated. Trying to do it without creating extra columns. Any help would be greatly appreciated.

=RIGHT(A11,LEN(A11) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A11&"0123456789")) -7)
=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")) 6),"")

CodePudding user response：

Here is few other alternatives,

• in cell B1

=IFERROR(AGGREGATE(14,6,--MID(A1,ROW($A$1:$A$102),6),1),"")

• in cell B2

=MAX(IFERROR(--MID(A2,ROW($1:$202),6),""))

• in cell B3

=IFERROR(TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A3," ","x"),ROW(INDIRECT("1:"&LEN(A3)-5)),6) 0),"000000"),"")*1

• in cell B4

=CHOOSECOLS(TEXTSPLIT(A1," "),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

• Here is one more, way to do,

=MAX(IFERROR(--TEXTSPLIT(A1,," "),0))

CodePudding user response：

This works based on leveraging the formula you already have. It's a little long, but it works on on all versions of Excel as well as google sheets, and is dynamic for length of string.

=LEFT(TRIM(SUBSTITUTE(A1,LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")) 6),"")),  
FIND(" ",TRIM(SUBSTITUTE(A1,LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")) 6),""))))

CodePudding user response：

If customer ID is always at end followed by '[Humcrypt]' and the same length then try:

=--LEFT(RIGHT(A1,17),6)

If not always the same length ID then try:

=--TEXTBEFORE(TEXTAFTER(A1," ",-2)," ")

Or:

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[last()]/preceding::*[1]")

If customer ID is always 6 digits and always followed but could be anywhere in the string then try:

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[string-length()=6][.*0=0]")

Note that you can alter the xpath to your needs. For example; if length is equal or larger than 6 then use //s[string-length()>=6][.*0=0]