Home Excel-VBA: Get numeric value from alphanumeric
Reply: 0

Excel-VBA: Get numeric value from alphanumeric

user1505 Published in July 20, 2018, 1:07 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
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
  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

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


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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307501 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO