remove all characters but the numbers

This is a discussion on remove all characters but the numbers within the Excel Questions forums, part of the Question Forums category; Please tell me there is a none VBA way to remove everything from a cell but the numbers. Example: A, ...

1. remove all characters but the numbers

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.

2. Re: remove all characters but the numbers

If the number is always inside parentheses try

=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)

3. Re: remove all characters but the numbers

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```

Sheet3
AB
7asdf4534asdf345343
Excel 2003

Worksheet Formulas
CellFormula
B7=onlynumbers(A7)

4. Re: remove all characters but the numbers

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.

Quality
BC
5#N/A224290
Excel 2007

Worksheet Formulas
CellFormula
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)

5. Re: remove all characters but the numbers

a small change to Peters' formula would do
Code:
`=MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)+0`

6. Re: remove all characters but the numbers

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

7. Re: remove all characters but the numbers

Awesome man. I learned something today and I appreciate it.

8. Re: remove all characters but the numbers

=-replace(a1,1,find("(",a1)-1,"")

9. Re: remove all characters but the numbers

VoG's formula worked great. But I'd like to understand your's as well. Can you walk me through it a bit?

10. Re: remove all characters but the numbers

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

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•