Extract only number from same cell

[Kristopher Chance]

I would like to extract only the number from the same cell, but there is too many character in the cell, Chinese wording, decimal point, slash and other things. I have list some of sample for reference

先94235127曾生/66932742/
先電*馬小姐94848842/98281231
尹生93100051/61088053
先 91410075 陳生67371896
收貨人:楊小姐96113854/90293009

What I need is extract the number and group it together, just like

先電*馬小姐94848842/98281231 -> 9484884298281231
998293829 / 66829188 -> 99829382966829188
先 91410075 陳生67371896 -> 9141007567371896

Is that possible?

 

[Rick Rothstein]

Then, even though it contains three IFERROR calls, I still think that this retains some simplicity:

=LARGE(IFERROR(0+MID($A1,ROW($1:$1000),8),""),1)&IFERROR(LARGE(IFERROR(0+MID($A1,ROW($1:$1000),8),""),2),"")

Yes, as long as the OP does not mind your formula mixing up the order of the two numbers on occasion.

 

[Rick Rothstein]

Here is an array entered** formula which will return the same values that the UDF which I posted earlier does...

=MID(SUMPRODUCT(--MID("01"&LEFT(A1,LEN(A1)/2),SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&LEFT(A1,LEN(A1)/2),ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)&MID(SUMPRODUCT(--MID("01"&MID(A1,1+LEN(A1)/2,99),SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&MID(A1,1+LEN(A1)/2,99),ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Here is a much shorter normal-entered formula (just enter it using the Enter key) which produces the same output (all the digits in the same order as the original text)...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),8)&MID(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+8,99),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+8,99)&"0123456789")),8)

The only difference between my original formula (or my UDF for that matter) and this formula is that my original formula (as well as my UDF) did not care if the digits were in groups of 8s or not, whereas this formula requires the number to be 8-digit numbers each

 

[XOR LX]

Yes, as long as the OP does not mind your formula mixing up the order of the two numbers on occasion.

Agreed. That's a potential issue.

Regards

 

[XOR LX]

Also this non-array:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&7^18)),8)&REPT(LOOKUP(70^70,0+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),SUMPRODUCT(0+ISNUMBER(0+MID(A1,ROW($1:$999),1)))>8)

Regards

 

[pgc01]

Hi

A tiny bit bigger

I was waiting for the answer from the OP about whether there are other digits.

Assuming there may be other digits in the string (groups less than 8) I tried this:

=IFERROR(MID(A1,SMALL(IF(MMULT(0+ISERROR(-MID($A1,ROW(INDIRECT("1:"&LEN(A1)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A1)-7))),1),8),"")
&IFERROR("/"&MID(A1,SMALL(IF(MMULT(0+ISERROR(-MID($A1,ROW(INDIRECT("1:"&LEN(A1)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A1)-7))),2),8),"")

I left a "/" between the 2 groups for testing purposes.

Although a bit big it's easy to expand. The second line is the same as the first one, just replace the 1 with 2. If we had for ex. up to 4 groups you'd just add more rows:

=IFERROR(MID(A4,SMALL(IF(MMULT(0+ISERROR(-MID($A4,ROW(INDIRECT("1:"&LEN(A4)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A4)-7))),1),8),"")
&IFERROR("/"&MID(A4,SMALL(IF(MMULT(0+ISERROR(-MID($A4,ROW(INDIRECT("1:"&LEN(A4)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A4)-7))),2),8),"")
&IFERROR("/"&MID(A4,SMALL(IF(MMULT(0+ISERROR(-MID($A4,ROW(INDIRECT("1:"&LEN(A4)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A4)-7))),3),8),"")
&IFERROR("/"&MID(A4,SMALL(IF(MMULT(0+ISERROR(-MID($A4,ROW(INDIRECT("1:"&LEN(A4)-7))+{0,1,2,3,4,5,6,7},1)),{1;1;1;1;1;1;1;1})=0,ROW(INDIRECT("1:"&LEN(A4)-7))),4),8),"")

which makes easy to adapt to the needs.


In this test in rows 2 and 4 I added other digits in the string that are ignored.



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">AB 90197611</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">90197611</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1A94235127B23C/66932742/</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">94235127/66932742</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A94235127AB/66932742/C1D23E456F93100051/<br>G61088053</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">94235127/66932742/93100051/61088053</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>

 

[Kristopher Chance]

The code is worked, I will tried for the rest of Database and Report you tomorrow.

Million Thanks

Also this non-array:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&7^18)),8)&REPT(LOOKUP(70^70,0+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),SUMPRODUCT(0+ISNUMBER(0+MID(A1,ROW($1:$999),1)))>8)

Regards

 

[Kristopher Chance]

I am not sure why, but the formula is not worked

The outcome after I place the code is this

先94235127曾生/66932742/ ->

先 914100/先 914100

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Kristopher Chance]

I think the code is work, I will try it with other database.

But actually, I don't need it in 8digits, since I only need all the number extract in one cell.

I will report you tomorrow

 

[Kristopher Chance]

This code is also work, but I will checked for another database as well.

Million Thanks

Will Report you tomorrow

 

[Kristopher Chance]

The formula is worked for the sample case that I provide, but some of data that more than 8 digit is not functioned

肛843876 / 97188047 (張小姐)

843876 /7188047

<colgroup><col><col></colgroup><tbody>
</tbody>

The Slash is still there

Also, I didn't realize that some of database is more than 16 digit (24 / 32)

But anyway it is functioned

Also this non-array:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&7^18)),8)&REPT(LOOKUP(70^70,0+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),SUMPRODUCT(0+ISNUMBER(0+MID(A1,ROW($1:$999),1)))>8)

Regards