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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Upvote 0
Is it always 5 characters?
Are the first 3 always letters?
The last 2 are always numbers?
If not, you could put more examples to try to find a pattern.
Hey,

Thank you for taking the time to reply. Yes, the first 3 letters are always letters, but the numbers will either be 3 or 4.
Thanks again for reaching out.
 
Upvote 0
GetNumeric is not a native Excel function. It is a User Defined Function that you create yourself in VBA!
See this link here: Extract Numbers from a String in Excel (Using Formulas or VBA)

Just add the code for the "GetNumeric" UDF to VBA and you can use it.
Hi Joe, thank you for reaching out. I have in fact tried to use this tutorial but its for 1 cell. I need the macro to cover a range of about 150-200 cells and simply copy the numbers only to the next..
Thanks for taking the time to reply.
 
Upvote 0
Thank you for taking the time to reply. Yes, the first 3 letters are always letters, but the numbers will either be 3 or 4.
Thanks again for reaching out.
Then for an entry in cell A1, you could simply use:
Excel Formula:
=MID(A1,4,4)
 
Upvote 0
Try this formula

Dante Amor
AB
1
2EJU1111
3EZY2222
4abc12341234
5abc789789
Hoja4
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,4,LEN(A2))+0
 
Upvote 0
Hi Joe, thank you for reaching out. I have in fact tried to use this tutorial but its for 1 cell. I need the macro to cover a range of about 150-200 cells and simply copy the numbers only to the next..
Thanks for taking the time to reply.
You can do that. Creating a UDF is just creating a function that you can then use either right on the spreadsheet or in VBA, like any other Excel function.

You can use the formula I provided in the previous post and just copy down for all rows.
Or you can easily have VBA do the same thing.
 
Upvote 0
Try this formula

Dante Amor
AB
1
2EJU1111
3EZY2222
4abc12341234
5abc789789
Hoja4
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,4,LEN(A2))+0
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 value.
 
Upvote 0
Solution
Try this formula

Dante Amor
AB
1
2EJU1111
3EZY2222
4abc12341234
5abc789789
Hoja4
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,4,LEN(A2))+0

Try this formula

Dante Amor
AB
1
2EJU1111
3EZY2222
4abc12341234
5abc789789
Hoja4
Cell Formulas
RangeFormula
B2:B5B2=MID(A2,4,LEN(A2))+0

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..
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top