Extract only number from same cell

K

Kristopher Chance

New Member
Joined
Apr 3, 2014
Messages
16
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Give this UDF (user defined function) a try...
Code: 
Function DigitsOnly(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  DigitsOnly = Replace(S, " ", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DigitsOnly just like it was a built-in Excel function. For example,

=DigitsOnly(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
But I would like to know is there alternative formula instead of the UDF


Rick Rothstein said:
Give this UDF (user defined function) a try...
Code: 
Function DigitsOnly(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  DigitsOnly = Replace(S, " ", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DigitsOnly just like it was a built-in Excel function. For example,

=DigitsOnly(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Click to expand...
 
Upvote 0
Do your desired extractions always consist of precisely 16 digits?

Regards
 
Upvote 0
Kristopher Chance said:
Nope, I only need the number is extracted and grouped together, since some of data may less or more than 16 digits.
Click to expand...

Then a non-VBA solution will not be feasible if the number of digits to extract is greater than 15, and assuming you wish to have all the digits in one cell. If you were content to have each digit extracted to its own cell, then yes, this could be achieved quite easily with formulas alone.

By the way, what's your objection to the UDF?

Regards
 
Upvote 0
Hi

Are these always 2 contiguous groups of digits?

I see in the example

1 group of 8 digits + 1 group of 8 digits
1 group of 9 digits + 1 group of 8 digits
1 group of 8 digits + 1 group of 8 digits

Is the second one correct, is each group of digits not always 8 digits?

Please clarify.

EDIT: I see that you already answered the second question, the groups don't have always to be 8 digits long.
The first question remains.
 
Last edited:
Upvote 0
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

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
So, can we say that it's always

Either

1 group of 8 contiguous digits

or

2 groups of 8 contiguous digits

??
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,967
Messages
6,122,503
Members
449,090
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top