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]

Yes I think so

 

[XOR LX]

Actually, it should be in 8 digits format, but some of data will only have 8 digit data just like the case showed below

孔生 90197611

<TBODY>
</TBODY>

You're not being very clear, I'm afraid. So was this:

998293829 / 66829188

which contains a 9-digit number, an error on your part?

Regards

 

[Kristopher Chance]

It is the error, so I think it docent need to care it

 

[XOR LX]

Based on what you've said, I don't see why this array formula** shouldn't work:

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

Copy to the right as required.

Note: the two strings won't necessarily be in the same order (from left to right) as they appear in the original string.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[XOR LX]

Edit: ignore the "copy to the right as required" part.

Regards

 

[Rick Rothstein]

Based on what you've said, I don't see why this array formula** shouldn't work:

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

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

It looks like your formula always assumes there are two numbers in the cell... did you perhaps miss this exchange in Messages 10 & 11?

So, can we say that it's always

Either

1 group of 8 contiguous digits

or

2 groups of 8 contiguous digits

??

Yes I think so

 

[Rick Rothstein]

It looks like your formula always assumes there are two numbers in the cell... did you perhaps miss this exchange in Messages 10 & 11?

Also, after you fix your formula for the above, you should remove the slash you are concatenating in the formula as the OP shows in his examples (in Message #1 that nothing is placed between the two numbers).

 

[Rick Rothstein]

I just afraid that I cannot handle the UDF only.

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

 

[pgc01]

Hi

Are there other digits in the strings, like

1 先 91410075 陳2 生67371896

or is it always just the 8 digit groups?

 

[XOR LX]

It looks like your formula always assumes there are two numbers in the cell... did you perhaps miss this exchange in Messages 10 & 11?

Also, after you fix your formula for the above, you should remove the slash you are concatenating in the formula as the OP shows in his examples (in Message #1 that nothing is placed between the two numbers).

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

Regards