Farhan Mahmood
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.
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
ASKER
Sir,
what is Difference which Convert Text to String???
Rgds
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
And then assuming your string is in A2, try this function on the sheet like below...=GetNumber(A2)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A Function cannot change the cell format so you need to pre-format the cells as a number format
ASKER
With the Help of @Shums Sir Code,
i Modified my code thnaks.
Regards Farhan
i Modified my code thnaks.
Regards Farhan
ASKER
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
Chears to Experts-Exchange.com and Community. Regards
You're Welcome Farhan!
Modification with your code;
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
ASKER
Dear Shums Sir,
Please Kindly guide what if i want to Extract 100.777 from String "Reading is 100.777"
Regards
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:
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")
You can add/remove "0" after decimal as per your need.
Open in new window