If the number is always inside parentheses try
=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)
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, ...
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.
If the number is always inside parentheses try
=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)
HTH, Peter
Please test any code on a copy of your workbook.
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
Sheet3Excel 2003
A B 7 asdf4534asdf3 45343
Worksheet Formulas
Cell Formula B7 =onlynumbers(A7)
regards
Sankar
show your sheet on the board, get HTML maker
யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா
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.
QualityExcel 2007
B C 5 #N/A 224290
Worksheet Formulas
Cell Formula 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)
a small change to Peters' formula would do
Code:=MID(H5,FIND("(",H5)+1,FIND(")",H5)-FIND("(",H5)-1)+0
regards
Sankar
show your sheet on the board, get HTML maker
யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா
Maybe this (it will discard leading zeros)
=MID(K4,FIND("(",K4)+1,FIND(")",K4)-FIND("(",K4)-1)+0
HTH, Peter
Please test any code on a copy of your workbook.
Awesome man. I learned something today and I appreciate it.
=-replace(a1,1,find("(",a1)-1,"")
Office 2007/2010
VoG's formula worked great. But I'd like to understand your's as well. Can you walk me through it a bit?
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
Office 2007/2010
Like this thread? Share it with others