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

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)

'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

share|improve this question
  • 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

4 Answers 4

active oldest votes
up vote 3 down vote accepted
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO