how do you set a variable in VBA (excel) from a partial cell value?

Question

Im trying to set a variable in VBA in excel by using just a part of a cell.

The cell contains "Number: XXXXXX"
where the X's are the number
I need the  number set  as a variable without the "number:" part in the variable. I can set the entire cell as a variable, but not part of it. Any idea how to do this?

garbo7441 · Accepted Answer

Sub StripText()
Dim i, LastRow, theNum
LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
For i = 1 To LastRow
For j = 1 To Len(Cells(i, "A"))
If Asc(Mid(Cells(i, "A"), j, 1)) = 58 Then
theNum = Mid(Cells(i, "A"), j + 2, _
Len(Cells(i, "A")) - (j - 1))
Cells(i, "B").Value = theNum
GoTo here
End If
Next j
here:
theNum = ""
Next i
End Sub

how do you set a variable in VBA (excel) from a partial cell value?

Related Questions

Trending Questions