Home Excel-VBA: Get numeric value from alphanumeric

# Excel-VBA: Get numeric value from alphanumeric

user7348
1#
user7348 Published in September 19, 2018, 9:09 am

I have a column (Column A) which has alphanumeric text and I want to read it and write it back to another column (Column C). The code is;

Sub getnumber()

'Define Variable
Dim anicode As Variant
Dim n As Long
Dim lastrowdata As Long

'Data Location
Sheets("Sheet1").Activate
lastrowdata = range("A2").end(xlDown).Row - 1

'Redefine Array
ReDim anicode(lastrowdata)

For n = 1 To lastrowdata
anicode(n) = Sheets("Sheet1").Cells(1 + n, 1)
Next n

'Altering Data
For n = 1 To lastrowdata
If IsNumeric(anicode(n)) Then
anicode(n) = NumericOnly
Else
End If
Next n

'Write Data
For n = 1 To lastrowdata
Sheets("Sheet1").Cells(1 + n, 3) = anicode(n)
Next n

End Sub


I am stuck at the Altering Data part where I want to get value from the text only. I only new in VBA and only know IsNumeric function currently. In the column A, the data is alphanumeric and is random, where it might have dash (-) or space ( ) or even jumble up with alphabet such as S2 or X4. There is possibility that the data is only numeric (since the data is long ~8k and will be growing).

As examples; in Column A, I have

R1-Adapa S2
R3-Omis 14
R4-189


and in Column C, I would like to have the numeric only

R1-002
R2-002
R3-014
R4-189


Appreciate if any possible function or any opinion regarding my problems or on my code. Thanks stackoverflow.com

• What if in A column we have Adapa S11 or R1-Adapa S2500? – Michał Turczyn Apr 17 at 5:18
• @MichałTurczyn for Adapa S11 will give result 011. The data only has max of 3 digits. Though, if it has above 3, it will be 2500. yet, I am sure that the value is up till 3 digits only. – Mohamad Faisal Apr 17 at 5:25