Like this:

Got another question on an excel list regarding splitting off the number of a mixed entry. The person had the following data:

PF309

P1009

P0009

…

And he wants to extract the numeric part of the string and display it as a number, sans leading zeros:

309

1009

9

…

There are several ways to do this and an excel guru might have gone to a formula for it, but being an old VB programmer I took the macro route and came up with the following (which is somewhat similar to the code that I used in a prior blog).

Public Sub ExtractNumeric()
Dim strText As String
strText = LCase(ActiveCell.Text)

Dim strNumericString As String

Dim i_inx As Integer

For i_inx = 1 To Len(strText)
Dim strCh As String
strCh = Mid(strText, i_inx, 1)
If IsNumeric(strCh) Then
strNumericString = strNumericString & strCh
End If
Next i_inx

Dim iNumber As Long
iNumber = CLng(strNumericString)

Dim i_row As Integer
Dim i_cell As Integer
i_row = ActiveCell.Row
i_cell = ActiveCell.Column

ActiveSheet.Cells(i_row, i_cell + 1).Formula = iNumber

End Sub

Run that as a macro on the cell you want to extract and you have your answer. You will find that a lot of problems in programming language can be boiled down to a for loop in one way or another (or it’s variant a do until/while. . .loop).

Advertisement