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)
'Read Data
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
R2-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
excel vba excel-vba
share|improve this question
edited Apr 17 at 5:52
Michał Turczyn
11.6k131937
asked Apr 17 at 2:26
Mohamad Faisal
377
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