TayoMayo,
Thanks for the workbook.
Sample raw data in worksheet Sheet1:
Excel 2007 |
---|
|
---|
| C |
---|
1 | 475 |
---|
2 | 347 lbs |
---|
3 | 53kg |
---|
4 | 4753kgs |
---|
5 | 4 lbs |
---|
6 | 534 LBS |
---|
7 | 53 KG |
---|
8 | 453 LB |
---|
9 | 45 KGS |
---|
10 | 34 LB |
---|
11 | 53 |
---|
12 | 453 |
---|
13 | 45 |
---|
14 | 34 |
---|
15 | 53 |
---|
16 | |
---|
|
---|
After the new macro with a new function:
Excel 2007 |
---|
|
---|
| C |
---|
1 | 475.0 |
---|
2 | 347.0 |
---|
3 | 116.8 |
---|
4 | 10478.6 |
---|
5 | 4.0 |
---|
6 | 534.0 |
---|
7 | 116.8 |
---|
8 | 453.0 |
---|
9 | 99.2 |
---|
10 | 34.0 |
---|
11 | 53.0 |
---|
12 | 453.0 |
---|
13 | 45.0 |
---|
14 | 34.0 |
---|
15 | 53.0 |
---|
16 | |
---|
|
---|
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below macro code, and, function
2. Open your NEW workbook
3. Press the keys
ALT +
F11 to open the Visual Basic Editor
4. Press the keys
ALT +
I to activate the Insert menu
5. Press
M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys
ALT +
Q to exit the Editor, and return to Excel
8. To run the macro from Excel press
ALT +
F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code:
Option Compare Text
Sub ExtractNumbers_V5()
' hiker95 , 08/01/2014, ME794797
Dim c As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
For Each c In .Range("C1", .Range("C" & Rows.Count).End(xlUp))
If InStr(c, "lb") Then
c.Value = TextNumV5(c, 0)
ElseIf InStr(c, "kg") Then
c.Value = TextNumV5(c, 0) * 2.2046226
End If
Next c
.Range("C1", .Range("C" & Rows.Count).End(xlUp)).NumberFormat = "0.0"
.Columns(3).AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub
Function TextNumV5(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' =TextNum(A1,1)
' 1 for Text only, 0 for Numbers only
With CreateObject("VBScript.RegExp")
.Pattern = IIf(ref = True, "\d+", "\D+")
.Global = True
TextNumV5 = .Replace(txt, "")
End With
End Function
You may have to add the Microsoft VBScript Regular Expressions 1.0 to the
References - VBA Project.
With your workbook that contains the above:
Press the keys
ALT +
F11 to open the Visual Basic Editor
In the VBA Editor, click on:
Tools
References...
Put a checkmark in the box marked
Microsoft VBScript Regular Expressions 1.0
Then click on the
OK button.
And, exit out of the VBA Editor.
Before you use the macro, and, function, with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension
.xlsm
Then run the
ExtractNumbers_V5 macro.