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

shanenicholson88

New Member
Joined
Oct 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
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
AB
1EJU123123
2EZY49494949
Hoja4
Cell Formulas
RangeFormula
B1:B2B1=MID(A1,4,LEN(A1))+0
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
Do you need it as a number value or as a text?

Dante Amor
AB
1EJU123123
2EZY49494949
Hoja4
Cell Formulas
RangeFormula
B1:B2B1=MID(A1,4,LEN(A1)-3)
 

shanenicholson88

New Member
Joined
Oct 6, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,841
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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
AB
1
2EJU1111
3EZY2222
4
5EJU123123
6EZY49494949
7HGF
8
9ABC09898
10
Extract Numbers


Data and results with that extra line of code included:

21 06 24.xlsm
AB
1
2EJU1111
3EZY2222
4
5EJU123123
6EZY49494949
7HGF
8
9ABC098098
10
Extract Numbers
 

Forum statistics

Threads
1,148,189
Messages
5,745,243
Members
423,936
Latest member
Conservatopia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top