VBA code to find number in string for a range of cells

[shanenicholson88]

Good morning all,

I am really struggling to solve this issue, any solutions are most welcome.
I need to create a macro to get the numbers from a string in cells a1:a200 and put just the numeric value in b2:b200. I am using excel 2010 and do not have the GetNumeric function available.

I simply need it look like this after the marco has ran..

Column A Column B
EJU11 11
EZY22 22

Thank you so much for your help.

 

[Joe4]

Did you try the formula I posted up in reply number 6?
It should do what you want, and will not drop any leading zeroes you may have.

 

[DanteAmor]

Hey,

The problem with this one is that its just taking the last 3 numbers and its a combination of 3 or 4 number...it could be EJU123 or EZY4949 so I need it to accurately pick up the the numbers from the string..

I don't understand what the problem is, the formula works for both examples.

Dante Amor
	A	B
1	EJU123	123
2	EZY4949	4949
Hoja4

Cell Formulas
Range	Formula
B1:B2	B1	=MID(A1,4,LEN(A1))+0

 

[DanteAmor]

Do you need it as a number value or as a text?

Dante Amor
	A	B
1	EJU123	123
2	EZY4949	4949
Hoja4

Cell Formulas
Range	Formula
B1:B2	B1	=MID(A1,4,LEN(A1)-3)

 

[shanenicholson88]

Just be careful with that option.
If the first number is a zero, that solution will drop the leading zero.

If you have leading zeroes that you want to keep, you will not want to add the "+0" to the end to coerce a numeric vaJoe,

Thanks Joe, I have dropped the 0 for now and it all seems to be working

 

[Joe4]

Thanks Joe, I have dropped the 0 for now and it all seems to be working

If that works, then the post I made way up in reply 6 before all of these other ones should work.

 

[Peter_SSs]

I need to create a macro to get the numbers from a string in cells a1:a200 and put just the numeric value in b2:b200.

Since you originally asked for a macro, here is one to consider.
There was some inconsistency about where the data and results are, so I have assumed starting in row 2, not row 1.
You also stated that you wanted the numeric part extracted. That implies leading zero(s) could be dropped & that is what my code would do. However, if you want a Text result and leading zeros retained, then simply un-comment the extra line in the code.

Sub ExtractNumbers()
  With Range("A2:A200")
'    .Offset(, 1).NumberFormat = "@"
    .Offset(, 1).Value = Evaluate(Replace("if(len(#),MID(#,4,4),"""")", "#", .Address))
  End With
End Sub

My sample data and results of the above code:

21 06 24.xlsm
	A	B
1
2	EJU11	11
3	EZY22	22
4
5	EJU123	123
6	EZY4949	4949
7	HGF
8
9	ABC098	98
10
Extract Numbers

Data and results with that extra line of code included:

21 06 24.xlsm
	A	B
1
2	EJU11	11
3	EZY22	22
4
5	EJU123	123
6	EZY4949	4949
7	HGF
8
9	ABC098	098
10
Extract Numbers