write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Monday, 11 July 2011

Extract Number & Text from string

1. Go to developer Tab else Press Alt + F11(http://msdn.microsoft.com/en-us/library/bb608625.aspx)
2.Click on Visual Basic icon
3.Go to Insert click on Module
4.Paste below code in standard module

worksheet formulas as shown in images

'Below User Define Function extract Number from string
Function ExtractNumber(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
ExtractNumber = ExtractNumber & Mid(rng.Value, i, 1)
End Select
Next i
End Function

'Below User Define Function extract Text from string
Function ExtractText(stdText As String)
Dim str As String, i As Integer
stdText = Trim(stdText)
For i = 1 To Len(stdText)
If Not IsNumeric(Mid(stdText, i, 1)) Then
str = str & Mid(stdText, i, 1)
End If
Next i
ExtractText = str
End Function

No comments:

Post a Comment