|
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great function for splitting numbers and text from cells and can help you with many of your text manipulation / extraction needs. This is especially helpful when separating product id numbers and names and conducting inventory management with spreadsheets, etc.
You can do a few things with this user defined function, including pulling only numbers or retaining decimal places or negative signs (dashes) if, in the original cell, the numbers contained these signs.
Where to install the macro: Module
UDF to Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel
Function GETNUMBER(rCell As Range, Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
Dim iCount As Integer
Dim i As Integer
Dim iLoop As Integer
Dim sText As String
Dim strNeg As String
Dim strDec As String
Dim lNum As String
Dim vVal As Variant
Dim vVal2 As Variant
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-"
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)
For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)
If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If
If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
Next iCount
GETNUMBER = CDbl(lNum)
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi, does anyone know how to display numbers only from a cell that contains both text and numbers? For example, I'd like to remove "Down" from the cell value "Down 77.95 (0.70%)"
I am using Excel 2000 and have received a large data file downloaded from a
mainframe system. Is there a way to easily convert the numbers stored as
text back to numbers so I can total them?
We have a software package from which we can export data to MS Excel.
However, when exported it arrives in Excel as text. Even though they are
numbers, they act like text. The only way to convert them text to numbers is
to edit the cell (press F2) and hit enter. I've tried every other method of
copying and pasting (even pasting special), but nothing seems to work. Any
ideas?
How do I copy only the numbers from a cell with numbers and text and symbols
to a new cell?
Dear All,
i have query, please help out.
multiple numbers and text are in single cell which are separated through spaces or comma, is there any formula which can bypass text and add all the numbers in the cell.
For example:
red 12, yellow 10, green 5
27
red 3, blue 9
12
yellow 21, blue 3
24
Thanks,
Gurmeet
In a spreadsheet I have headings for blood-test values (i.e. RBC, WBC, etc.).
The values below sometimes have text after to indicate the value is (L) -
low, or (VL) - v. low, such as: "23.5 (VL)". Is there a way to use the values
in cells that have text as values, that is to always ignore text and treat
numbers within as numbers. There are many, many other spreadsheets where this
ability w/b very beneficial. Thank you!
Separate numbers from text from a cell in excel.
For example:-
Data
Result required
123337813 bi
123337813
gft 76423715 nh
76423715
hjj98754699 yu
98754699
hfjf 787252236
787252236
Can anyone tell me please how to extract numbers from text in a cell. Example Andrew, 20055, Keith where the number in the middle is sometimes 2, 3, 4, 5 or 6 in length? thanks
I use Excel 2003.
There are six columns (2000 rows deep) and each cell contains a number from 1 to 50.
I wish to know how many times a combination of two numbers appears in all the rows. e.g if I select the two numbers 14 and 47 I want the formula to tell me how many times those two numbers appear in all of the rows.
Thank you.
Colwyn.
Hi, all of you!
I have seeing that sometimes there are excel worksheet taken from the web or exported from other applications that show numbers but they are texts and is not possible to operate with them. I could note two kind of problems:
A.- Sheet received from a guy living in USA. Dot for decimal and comma for thousands. My Excel interpret those numbers as text. Solution? 1.- Select all the numbers area. 2.- CTRL B to look for all dots (.). 3.- Replace all of them for exclamation character (!). 4.- CTRL B to look for all commas (,). 5.- Replace all of them for dots (.) 6.- CTRL B to look for exclamations characters (!). 7.- Replace all of them for commas (,). Now, my Excel interpret all numbers as numbers and I can operate with them.
B.- Sheet received from the web or exported from other application. Just put in an additional cell a one (1) and in other cell multiply the text-number cell by the one cell. The result is a number-number cell. I do not know why but it works.
|
|
