extract only the numbers between Parenthesis

[jcbek11]

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?

 

[jcbek11]

I have a different question if you're allowed to help me with an additional question:

In Column C the full address of a company is located there. For instance: 2234 164th ST SW STe 204 Lynnwood, WA 98087

I need to extract the City out and put it in a column all of it's own and I need to extract the Zip code out and put it in a column all of it's own.
I need to cycle through all 520 records doing this...

Easy function to write?

 

[Blade Hunter]

It's a function, you can call it within a spreadsheet like this =GetNumber(A1) whre A1 is where your value resides. Then just drag it down.

If you are hell bent on using code to populate it, don't do this:

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

Do this instead

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

Hope that helps

Dan

 

[Rick Rothstein]

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

Why were you so insistent on a VBA solution when there is a formula solution available? Put this formula in Cell B3 and copy it down to B520..

=MID(LEFT(A1,FIND(")",A1&")")-1),FIND("NMLS",A1&"NMLS")+4,99)

 

[Blade Hunter]

These excel formulas will get you the parts you want:

Excel Workbook
	A	B	C	D
1	Address	Third last	Second last	Last
2	2234 164th ST SW STe 204 Lynnwood, WA 98087	Lynnwood,	WA	98087
Sheet1

Hope that helps

Dan

 

[jcbek11]

More familiar with VBA and didn't want the formula in the Worksheet. Wanted the actual number to be in the cell.

Were you able to take a look at my additional question Rick?

Thanks,
Jeff

 

[jcbek11]

Wow. Thanks for the Formulas... I usually look for VBA solutions but I used your formulas. The only issue is in the first formula it brought the comma along with the data and I don't need that...
How can I edit that formula to loose the comma?

Thanks for your efforts.

 

[Rick Rothstein]

Were you able to take a look at my additional question Rick?

It looks like Blade Hunter has answered that question for you... or are you saying you want that as a VBA solution as well?

 

[Blade Hunter]

I was going to use a substitute which would replace all commas with "" but there could be a genuine on in there so I have just chopped the last char off the result:

=LEFT(TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),765),255)),LEN(TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),765),255)))-1)

VBA is good, my entire career is based upon VBA but don't be too quick to sell Excel short on it's formulas, functions and array formulas, there are some wonderful parts to it that really don't need to be built in VBA.

Cheers

Dan

 

[jcbek11]

Rick and Blade Hunter you two have saved me hours of work. Thank you so much. I really appreciate your work and efforts.
I hope you have a great day / night where ever you may live. Thank you again.

 

[jolivanes]

Would this be better?

Sub Fill_It()
    Dim lr As Long
    lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    Range("B3:B" & lr).Value = "=GetNumber(RC[-1])"
End Sub

Note: While working this out, you got all the answers.
You guys are way too fast for me!!!!