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.

 

[TayoMayo]

hiker95,
In your reply I can only see 1 line of code to remove if it has an error.

 

[Rick Rothstein]

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.

Give this a try...

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("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

 

[TayoMayo]

Mr. Rothstein,
Your code does a perfect job for the numbers that have a kg in the cell, but it returns all other cells (those without a designation and those with lbs) as #N/A. Am I missing something or is that the output it is supposed to have?

hiker95,
I attempted to run your code, but it kept giving me an eror saying "Compile error: User-defined type not defined". I tried removing the one line I can see from your last post and it made no difference.

 

[Rick Rothstein]

Mr. Rothstein,
Your code does a perfect job for the numbers that have a kg in the cell, but it returns all other cells (those without a designation and those with lbs) as #N/A. Am I missing something or is that the output it is supposed to have?

It does not do that for me when I copy what you posted in Message #14 into Column C of a worksheet (plus add some number only cells) and run my code against it. Can you post a copy of your workbook to some free file sharing site (you can use http://www.box.net/files if you do not have one of your own) so that we can see the data you actually have in the cell?

 

[TayoMayo]

Ok I have an account on box.com but I do not know how to share the workbook I have been using to test the macros

 

[hiker95]

TayoMayo,

hiker95,
I attempted to run your code, but it kept giving me an eror saying "Compile error: User-defined type not defined". I tried removing the one line I can see from your last post and it made no difference.

In my last reply for the ExtractNumbers_V4 macro, I forgot to add the following instructions:

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


If you are still having a problem, then when you post your latest raw data on BOX, I will have a look.

 

[Rick Rothstein]

Ok I have an account on box.com but I do not know how to share the workbook I have been using to test the macros

box.com is different than the link I posted which was for box.net so I am not sure how that one works. Maybe they have a tutorial on their site which shows you how to share a file.

 

[Rick Rothstein]

box.com is different than the link I posted which was for box.net so I am not sure how that one works. Maybe they have a tutorial on their site which shows you how to share a file.

While I believe others reading this thread would like to see your file, you could email it directly to me if you want. My email address is...

rick DOT news AT verizon DOT net

Please include the thread title in your message so I can relate the file back here more easily.

 

[Rick Rothstein]

you could email it directly to me if you want.

I got your file and the reason the UDF does not work for you is you installed it in the wrong location... you put is in a Sheet Module rather than a General Module. See if this general set of instruction that I have posted in the past helps you install it in the correct location.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[TayoMayo]

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?