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.

 

[hiker95]

TayoMayo,

Have you even tried my macros?

 

[iliauk]

Why not a CSE formula?

E.g.

526 lbs	526
526 LBS	526
526Lbs	526
526 lbs.	526
526 LBS.	526
526lbs	526
148 LBS	148
148 lbs.	148
148 lb	148
469lb.	469
469lb.	469
148lb	148

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$12),1)),0),COUNT(1*MID(A1,ROW($1:$12),1)))}

 

[Rick Rothstein]

Mr. Rothstein,
Is there a way to modify your code to also do a conversion? So for the data I posted earlier, if there was a kg measurement in there, is it possible for it to convert it to lbs, then delete the kg text from the cell? So at the end, there would be a column of only numbers that is all in pound measurements, even if it was originally in kilograms.

I assume from your posted data that values in Kilograms appear as numbers only (without any text in the cell with them). If so, just adding the line I show in red should do what you want...

Sub LeaveNumbersOnly()
  Dim LastRow As Long, UnusedCol As Long
  Const StartRow As Long = 1
  Const ColNum As Long = 3
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
  UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
  With Cells(StartRow, ColNum).Resize(LastRow - StartRow + 1)
[COLOR=#FF0000][B]    .Value = Evaluate(Replace("IF(ISNUMBER(C1:C#),2.2046226*C1:C#,C1:C#)", "#", LastRow))[/B][/COLOR]
    .Offset(, UnusedCol - ColNum).FormulaR1C1 = "=LOOKUP(9.9E+307,--LEFT(RC" & ColNum & ",ROW(R1:R99)))"
    .Value = .Offset(, UnusedCol - ColNum).Value
  End With
  Columns(UnusedCol).Clear
  Application.ScreenUpdating = True
End Sub
[COLOR=#FF0000][/COLOR]

 

[TayoMayo]

Hiker95,
Yes I used your macros and they worked very well. I just was looking to modify one of the codes that were posted and thought it might be easier to modify the other because it looked simpler.

Mr. Rothstein,
If the data still has the kg text in the cells, how would the code change? So if the data looked like what is posted below, how would be code be effected? I am trying to understand how all of this is working so that hopefully I will be able to construct my own version of it next time a problem like this arises.

526 lbs
526 kg
526Lbs
526 lbs.
526 KG
526lbs
148 LBS
148 kg.
148 kg
469lb.
469kg
148lb

<tbody>
</tbody>

 

[hiker95]

TayoMayo,

New sample raw data:

Excel 2007
	C
1	526 lbs
2	526 kg
3	526Lbs
4	526 lbs.
5	526 KG
6	526lbs
7	148 LBS
8	148 kg.
9	148 kg
10	469lb.
11	469kg
12	148lb
13
Sheet1

After the new macro:

Excel 2007
	C
1	526
2	1159.631488
3	526
4	526
5	1159.631488
6	526
7	148
8	326.2841448
9	326.2841448
10	469
11	1033.967999
12	148
13
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).

Option Compare Text
Sub ExtractNumbers_V3()
' hiker95 , 07/30/2014, ME794797
Dim c As Range
Application.ScreenUpdating = False
Dim RE As RegExp
Set RE = New RegExp
RE.Pattern = "\D"
RE.Global = True
For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
  If InStr(c, "lb") Then
    c.Value = RE.Replace(c.Text, "")
  ElseIf InStr(c, "kg") Then
    c.Value = RE.Replace(c.Text, "") * 2.2046226
  End If
Next c
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

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 with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractNumbers_V3 macro.


What type of formatting would you like (number of decimal places) for the cells that contained kg?

 

[TayoMayo]

Thank you very much for the code, it works wonderfully. I am to concerned with the number of decimal places at this point. I really only need 1 decimal place, and I was going to format the cells in excel to only show 1 decimal. If that didnt work then I was just going to modify the multiplier that is in the code.

 

[TayoMayo]

How would I change the column that was being processed? The code works for the sample data that posted perfectly, but for the larger amount of data that I am working with, it will need to be applied to different columns.

 

[Rick Rothstein]

Mr. Rothstein,
If the data still has the kg text in the cells, how would the code change? So if the data looked like what is posted below, how would be code be effected? I am trying to understand how all of this is working so that hopefully I will be able to construct my own version of it next time a problem like this arises.

526 lbs
526 kg
526Lbs
526 lbs.
526 KG
526lbs
148 LBS
148 kg.
148 kg
469lb.
469kg
148lb

<tbody>
</tbody>

Just to keep this thread update, here is the modification to my code which will handle cells with or without "kg" in them as kilograms and convert them to pounds...

Sub LeaveNumbersOnly()
  Dim LastRow As Long, UnusedCol As Long, Addr As String
  Const StartRow As Long = 1
  Const ColNum As Long = 3
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
  UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
  Addr = Cells(1, ColNum).Resize(LastRow).Address
  With Cells(StartRow, ColNum).Resize(LastRow - StartRow + 1)
    .Value = Evaluate(Replace("IF(ISNUMBER(@),2.2046226*@,IFERROR(2.2046226*LEFT(@,SEARCH(""kg"",@)-1),@))", "@", Addr))
    .Offset(, UnusedCol - ColNum).FormulaR1C1 = "=LOOKUP(9.9E+307,--LEFT(RC" & ColNum & ",ROW(R1:R99)))"
    .Value = .Offset(, UnusedCol - ColNum).Value
  End With
  Columns(UnusedCol).Clear
  Application.ScreenUpdating = True
End Sub

If you want to apply this code to other columns, simply change the column number being assigned to the ColNum constant in the highlighted Const statement.

 

[hiker95]

TayoMayo,

After the newest macro:

Excel 2007
	C
1	526.0
2	1159.6
3	526.0
4	526.0
5	1159.6
6	526.0
7	148.0
8	326.3
9	326.3
10	469.0
11	1034.0
12	148.0
13
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).

Option Compare Text
Sub ExtractNumbers_V4()
' hiker95 , 07/30/2014, ME794797
Dim c As Range
Application.ScreenUpdating = False
Dim RE As RegExp
Set RE = New RegExp
RE.Pattern = "\D"
RE.Global = True
For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
  If InStr(c, "lb") Then
    c.Value = RE.Replace(c.Text, "")
  ElseIf InStr(c, "kg") Then
    c.Value = RE.Replace(c.Text, "") * 2.2046226
  End If
Next c
Range("C1", Range("C" & Rows.Count).End(xlUp)).NumberFormat = "0.0"
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractNumbers_V4 macro.



If you get an error concerning the next line of code, then, in the module where you have the macro, delete one of the two, following lines of code:

Option Compare Text

 

[TayoMayo]

Mr. Rothstein,
Is it possible to change it slightly so that it will only convert the cells that contain "kg" in them? If there is a cell without either kg or lbs, then it is already in pounds. I apologize that it is taking so much time and effort to try and get this right.