Link to home
Start Free TrialLog in
Avatar of Farhan Mahmood
Farhan MahmoodFlag for Pakistan

asked on

VBA Function Help_3

Dear Respectable Experts,

i need help below code attached function Extract a number from a string value and returns numbers as string i need that it returns number as value or number please help me if it is possible.

Thanks.

Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = Result
 
End Function

Open in new window

Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi Try below:
Function NumbersOnly(n As Variant)

Dim MyNumbers As String, i As Integer

    For i = 1 To Len(n)
        If IsNumeric(Mid(n, i, 1)) Then
            MyNumbers = MyNumbers & Mid(n, i, 1)
        End If
    Next i

NumbersOnly = MyNumbers * 1

End Function

Open in new window

Avatar of Farhan Mahmood

ASKER

Sir,

what is Difference which Convert Text to String???

Rgds
Do you want result to be formatted as number after extraction?
Give this a try...
Function GetNumber(ByVal Rng As Range)
Dim Num As Long
Dim RE As Object, Match As Object, Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
   .Global = True
   .Pattern = "\d"
End With
If RE.test(Rng.Value) Then
   Set Matches = RE.Execute(Rng.Value)
   For Each Match In Matches
      Num = Num & Match.Value
   Next Match
End If
GetNumber = Num
End Function

Open in new window

And then assuming your string is in A2, try this function on the sheet like below...
=GetNumber(A2)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A Function cannot change the cell format so you need to pre-format the cells as a number format
With the Help of @Shums Sir Code,

i Modified my code thnaks.

Regards Farhan
Sir, Your Solution help me to find my code problem and modification thanks Aloot but if you could reply with my code modification then its was great ... but now it is also Great Thanks Aloot  God Bless You Always!.
Chears to Experts-Exchange.com and Community. Regards
You're Welcome Farhan!

Modification with your code;
Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = WorksheetFunction.Text(Result, "#,##0.00")
 
End Function

Open in new window

Dear Shums Sir,

Please Kindly guide what if i want to Extract 100.777 from String "Reading is 100.777"

Regards
Hi Farhan,

I am not sure about editing your Function to get expected result. Assuming you have Strings in A2, then try below formula in B2 and drag down:
=TEXT(LOOKUP(9.9E+307,--LEFT(MID(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A3&"1023456789")),999),ROW(INDIRECT("1:999")))),"#,##0.000")

Open in new window

You can add/remove "0" after decimal as per your need.