VideoPhoto

Showing posts with label extract numbers. Show all posts
Showing posts with label extract numbers. Show all posts

04 May, 2021

Pull number from alphanumeric string in a Cell

Sometimes you may need to pull the numbers from alphanumeric strings in your worksheet cells.  The user defined function (UDF), I’m presenting here, separates numbers from a cell containing numbers and text characters. The referenced cell can contain any string, including spaces, decimal points or negative numbers.

Function PullNum(rng As Range, _
     Optional Point As Boolean, Optional Negat As Boolean) As Double
'Pulls a number from a cell containing alphanumerics
'e.g. =PullNum(A1,,TRUE) pulls negative number
    Dim cnt As Integer, i As Integer, sLen As Integer
    Dim sTxt As String, sMinus As String, sDP As String, sN As String
    Dim sV As Variant
    sTxt = rng
    If Point = True And Negat = True Then
        sMinus = "-": sDP = "."
    ElseIf Point = True And Negat = False Then
        sMinus = vbNullString: sDP = "."
    ElseIf Point = False And Negat = True Then
        sMinus = "-": sDP = vbNullString
    End If
    sLen = Len(sTxt)
    For cnt = sLen To 1 Step -1
    sV = Mid(sTxt, cnt, 1)
        If IsNumeric(sV) Or sV = sMinus Or sV = sDP Then
            i = i + 1
            sN = Mid(sTxt, cnt, 1) & sN
                If IsNumeric(sN) Then
                    If CDbl(sN) < 0 Then Exit For
                Else
                  sN = Replace(sN, Left(sN, 1), "", , 1)
                End If
        End If
    If i = 1 And sN <> vbNullString Then sN = CDbl(Mid(sN, 1, 1))
    Next cnt
    PullNum = CDbl(sN)
End Function

To use the function, copy its code to any of the Modules in your workbook (add a Module, if not included yet in the workbook). The function can be used in several formats, depending on your optional needs, e.g.:

=pullnum(A1)
=pullnum(A1,TRUE)
=pullnum(A1,TRUE,TRUE)
=pullnum(A1,,TRUE)
=pullnum(A1,FALSE,TRUE)

Here are some examples of output obtained with the function:

 

 

28 April, 2021

Extracting Numerical and/or Non-numerical characters from a string

When dealing with data in Excel, there are situations, where you have both numbers and text in the same cells. Sometimes you may need to extract (or show separately) numbers and text, in order to easier analyze those values. In such cases I would recommend using the following macro to make such a task easier and efficient.

Sub ExtractBoth()
'Extracts separately numerical & non-numerical characters from a string in an active cell
Dim exTxt As String
Dim exNum As Double
Dim myStr As String
myStr = ActiveCell.Value
Dim outp As String
For i = 1 To Len(myStr)
If Val(Mid(myStr, i, 1)) Or Mid(myStr, i, 1) = "0" Then
    exNum = exNum & Mid(myStr, i, 1)
Else
    exTxt = exTxt & Mid(myStr, i, 1)
End If
Next i
ActiveCell.Offset(, 1).Value = exNum
ActiveCell.Offset(, 2).Value2 = exTxt
outp = MsgBox("Extracted number: " & exNum & vbNewLine & "Extracted text: " & exTxt, , "Extracted digits and text")

End Sub

If your active cell is located e.g. in cell A1 and contains string like this one:
ab&400er=29>-23sdrk@j89:?
then the macro separates it into two parts like these:
400292389    and     ab&er=>-sdrk@j:?
and displays them in cells B1 and C1. Then, optionally, it shows them for you in a message box.
 
The macro code can be easily adapted to your specific needs.