How can i extract the numeric part of a cell content in Excel?

user_13230527 0
Viewed 256

I am working on different set of data an would like to extract only numeric part of them from an excel sheet. How could I do to extract only the numeric part please?

For example:

  • "A51sd5dd5$1" I just need 51551
  • "Bd203m01,02" I just need 2030102

I need of an Excel formula, or Vba function if you may help please. Thank you in advance for your help

excel-formula vba excel
2 Answers

Welcome in the Forum! I think this is the kind of function you need to have. I tested your examples and they are giving the right value in numeric of course.

Here is the function:

Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef) 'This is the Length of the CellRef entry

Dim n As Long
Dim sResult As String   'Store in a result variable the numeric characters

For n = 1 To StringLength
    'iterate through each character and test if it is numeric
    If IsNumeric(Mid(CellRef, n, 1)) Then sResult = sResult & Mid(CellRef, n, 1)
Next n

'Return your value
GetNumeric = sResult
End Function

I hope it will help you!

user_73581880

If you have Office 365 and TEXTJOIN function available to you then you can use below formula assuming your data is in cell A1.

=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)+0,""))

EDIT:

Following is a formula which you can use for Excel Versions later than 2007. If you have older than 2007 then IFERROR part needs to be re-worked.

=NPV(-0.9,IFERROR(MID(A1,(LEN(A1)+1)-ROW($A$1:INDEX(A:A,LEN(A1))),1)*0.1,""))

user_87599270
Related