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?

 

[Kristopher Chance]

Since the formula is focusing on 8 digits, but some of data is more than 16 so the formula cannot abstract all the data.

Also I am not sure why there is one or 2 data is not functioned

998136951 / 24599756	998136951 / 2459
998293829 / 66829188	998293829 / 6682
998513117 / 92581987 (先)	998513117 / 9258

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

Since i think you force the code grouped in 8 digits, so if the data is grouped in 9 digits, it will not be functioned

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

 

[Kristopher Chance]

This formula is very good and process nearly all data. Because of this formula, I find that some of my data base is more than 16 digits just like 24 and 32.

However, i am not sure why but I got only one data is not worked, please check it below.
先TONY95505778/68389218/92898621/9272175 -> 9550577868389218928986219270000

If the digit is more than 27, it will start as 0 after.

But anyway this array formula is the most accurate, I will test it in a larger database to see is there any problems.

Thanks for your help.

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

 

[Rick Rothstein]

This formula is very good and process nearly all data. Because of this formula, I find that some of my data base is more than 16 digits just like 24 and 32.

However, i am not sure why but I got only one data is not worked, please check it below.
先TONY95505778/68389218/92898621/9272175 -> 9550577868389218928986219270000

If the digit is more than 27, it will start as 0 after.

But anyway this array formula is the most accurate, I will test it in a larger database to see is there any problems.

You are starting to get enough variability that perhaps a formula solution is not the answer anymore. I would like to suggest you take a look at my UDF in Message #2 again... it has no limit on the number of digits it can retrieve... well, there is a limit but you will never reach it (we are talking more than two billion plus characters). I provided instructions in that message on how to install the code and use it, I do not believe you will have any trouble implementing it.

 

[Kristopher Chance]

Okay, I will do it in UDF in message.

Thanks for your help and recommendation

 

[Kristopher Chance]

Also I have another questions.

For the Marry Formula, If my unprocessed data is in Cell B2, which part I should make changes? I have tried to make A1 to B2, but it is not work for the formula.

=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,30

Regards