extract only the numbers between Parenthesis

jcbek11

New Member
Joined
Jun 28, 2013
Messages
48
I would like to know what the easiest VBA function is to extract only the numbers that are between Parenthesis

For example if I had a cell with (NMLS343432) and I wanted to only extract the number and put it into the cell next to
the cell with the value.

What is the shortest VBA function that could do this?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I would like to know what the easiest VBA function is to extract only the numbers that are between Parenthesis

For example if I had a cell with (NMLS343432) and I wanted to only extract the number and put it into the cell next to
the cell with the value.

What is the shortest VBA function that could do this?
We need some more information from you...

1) What is the "number" to you.... the 343432 or the NMLS343432?

2) Could there be other text in the cell with that "number" in parentheses or is that the only thing that will be in the cell?

3) If the answer to Question #2 is that there could be other text, then could there be more than one "number" in parentheses in that text and, if so, what did you want return and in what manner (delimited string, array, something else)?
 
Upvote 0
Looks like this:

####(NMLS######)

Where # = random numbers

Also sometimes there are only 3 numbers after the NMLS or sometimes 8 numbers.
The important thing to me is the numbers next to the NMLS in the parenthesis.
Nothing else is relevant.

Does this answer the 3 questions? If not please ask anything else you need.

Thank you for taking a look at my question.
 
Upvote 0
1. Only need the numbers in the parenthesis that is next to NMLS
2. No other text in the Parenthesis other than NMLS
3. I would like the number next to NMLS Returned
 
Upvote 0
I would like to know what the easiest VBA function is to extract only the numbers that are between Parenthesis

For example if I had a cell with (NMLS343432) and I wanted to only extract the number and put it into the cell next to
the cell with the value.

What is the shortest VBA function that could do this?

Is the text in front of the the number always NMLS (in upper case)? If so...

Code:
Function GetNumber(S As String) As String
  If InStr(S, "NMLS") Then GetNumber = Val(Split(S, "NMLS")(0))
End Function
 
Upvote 0
Hi Rick,

Thank you for that function.... However it gives me the numbers that are in front of the NMLS not the numbers to the right of NMLS that are inside the Parenthesis.

I only need the numbers that are located inside the Parenthesis.

Thank you.
 
Upvote 0
Hi Rick,

Thank you for that function.... However it gives me the numbers that are in front of the NMLS not the numbers to the right of NMLS that are inside the Parenthesis.
Whoops! That 0 was supposed to be a 1...

Rich (BB code):
Function GetNumber(S As String) As String
  If InStr(S, "NMLS") Then GetNumber = Val(Split(S, "NMLS")(1))
End Function
 
Upvote 0
You are awesome Rick!!! I wish I could get your help with all my questions. :) Crazy how a 0 and 1 could trick this formula. Thank you very much. :)
Now I need to write a loop to get it to evaluate all the 500 cells. :)
 
Upvote 0
You are awesome Rick!!! I wish I could get your help with all my questions. :) Crazy how a 0 and 1 could trick this formula. Thank you very much. :)
Now I need to write a loop to get it to evaluate all the 500 cells. :)
Where did you want the out from the loop to go to... cells on a worksheet, an array or a delimited text string?
 
Upvote 0
I just wrote it like this:

Dim i As Integer


Range("B3").Select
For i = 3 To 520

Range("B" & i).Select

ActiveCell.Value = GetNumber(Range("A" & i))


Next


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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