remove all characters but the numbers

spanspace

Board Regular
Joined
Jan 3, 2007
Messages
159
Please tell me there is a none VBA way to remove everything from a cell but the numbers.

Example: A, DAVID (002081) becomes 002081

The cell the formula will go in is F4 and the cell its looking at is K4.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If the number is always inside parentheses try

=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)
 
Upvote 0
maybe this

Code:
Function onlynumbers(ByVal ref As String)
Dim rx As Object

Set rx = CreateObject("VBScript.RegExp")

With rx
    .Pattern = "\D"
    .Global = True
    onlynumbers = .Replace(ref, "")
End With
End Function
Excel Workbook
AB
7asdf4534asdf345343
Sheet3
Excel 2003
Cell Formulas
RangeFormula
B7=onlynumbers(A7)
 
Upvote 0
VoG that worked great to a point. It removed everything but the number but the when the cell to the left of it tries to use it as a reference it returns a #N/A. If I manually type the number into the cell then the to the left works fine.
Excel Workbook
BC
5#N/A224290
Quality
Excel 2007
Cell Formulas
RangeFormula
B5=VLOOKUP(C5,Agents!$J$2:$Q$640,MATCH("4DIGIT",Agents!$J$2:$Q$2,0),FALSE)
C5=MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)
 
Upvote 0
a small change to Peters' formula would do
Rich (BB code):
=MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)+0
 
Upvote 0
Maybe this (it will discard leading zeros)

=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)+0
 
Upvote 0
VoG's formula worked great. But I'd like to understand your's as well. Can you walk me through it a bit?
 
Upvote 0
Sure, Excel treats numbers in parentheses as negative numbers.

So all you have to do is replace from the 1st character to just before the first parentheses with nothing.

By putting the - sign in front, you are effectively applying math to the text value, thus coercing it to a number, and negating the negative value which will return a positive value.

-(002081) is equal to 2081
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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