Delte Text from a column

[TayoMayo]

Hey everyone,
I am pretty new to VBA and I am wondering if there is a way to delete text from a colum that has both numbers and letters in it. For example, if the column is one that is holding weight values, I would need to get rid of all the lbs, LBS, lbs., etc from the column and leave only the text. Is there a way to do this? I don't have any code to post, because I have no idea how to go about starting. Thank you for any and all replies.

 

[Rick Rothstein]

Mr. Rothstein,
I have done exactly what your instructions said in multiple new books to try and get it to work, but it always returns the same output. Do I need to add the tools or something that hiker was refering to earlier in the post?

Did you remember to delete the code you had in the Sheet Module? On the file you sent me, I moved the good data you showed in Column E over to Column C (replacing the bad results you showed from a prior running of my code), then I deleted the code from the Sheet1 Module, inserted a general Module (as I described in Message #29) and ran the code... it produced the expected results in Column C without any errors. I do not know why doing that does not work for you since I cannot duplicate the problem you describe, using your own workbook, so I am at a loss as to what to tell you to do now.

 

[hiker95]

TayoMayo,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


If you can send your workbook via e-mail, why can't you post your workbook on BOX, with sensitive data changed.


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[TayoMayo]

attached is the link to the file uploaded on Box. this is a completelly new book to test the macro. I deleted all the other files and macros that I had before so that there should be no accidental running of any other macros. I am using Excel 2007 or PC. https://app.box.com/s/qmujwoyhkzjy7ot70xfr

 

[hiker95]

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
Sheet1

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
Sheet1

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.

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.

 

[TayoMayo]

hiker95,
Thank you very much. That code works perfectly. The only question I have now is if I needed to change the column that is being processed by this code, what do I need to modify?

 

[Rick Rothstein]

hiker95,

Can you do me a favor? Would you run the code I posted in Message #22 against the workbook you downloaded and tell me if it works for you or not ? Thanks.

 

[hiker95]

TayoMayo,

Thanks for the feedback.

You are very welcome. Glad I could help.

if I needed to change the column that is being processed by this code, what do I need to modify?

You would change the two C's in the following line of code to the correct column letter.

  For Each c In .Range("[B][SIZE=3]C[/SIZE][/B]1", .Range("[B][SIZE=3]C[/SIZE][/B]" & Rows.Count).End(xlUp))

 

[Rick Rothstein]

You would change the two C's in the following line of code to the correct column letter.

For Each c In .Range("[B][SIZE=3]C[/SIZE][/B]1", .Range("[B][SIZE=3]C[/SIZE][/B]" & Rows.Count).End(xlUp))

You would also change these lines too, correct?

  .Range("C1", .Range("C" & Rows.Count).End(xlUp)).NumberFormat = "0.0"
  .Columns(3).AutoFit

The same two C's and change the 3 as well.

 

[hiker95]

Rick Rothstein,

Nice catch - thanks.

 

[hiker95]

TayoMayo,

if I needed to change the column that is being processed by this code, what do I need to modify?

You would change the two C 's in the following line of code to the correct column letter.

  For Each c In .Range("C1", .Range("C" & Rows.Count).End(xlUp))

And, in the next line of code, C = 3:

  .Columns(3).AutoFit

Column A = 1
Column B = 2, and, so on....


If you need further help, let me know the new column, and, I will give you the correct number for that column.