Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
how do you set a variable in VBA (excel) from a partial cell value?
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?
Sorry, I should have been clearer, The "number" contains alphanumeric charecters. But I see what you did... I'll see if I can change it around.
Thanks alot for your help.
1 Answer
- garbo7441Lv 71 decade agoFavorite 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
