Delte Text from a column

T

TayoMayo

New Member
Joined
Jul 24, 2014
Messages
22
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
I assume that you mean leave only the numbers (not text).

Can you post a representative sample of the values that you have, and which column are they in?
 
Upvote 0
VoG said:
I assume that you mean leave only the numbers (not text).

Can you post a representative sample of the values that you have, and which column are they in?
Click to expand...
Another question in addition to VoG's question...

Are you wanting to physically change the values within their current cells or display the number in a different column (on the same row)?
 
Upvote 0
To answer your questions, yes I mean to keep the numbers not the text, and I would like to keep them on the same row, only deleting the text. Below is a few columns of what I have to work with. The column that needs to be changed is the second from the right (it should be the only one with text and numbers). I apologize if the formatting is difficult to transfer, I could not figure out how to put an excel spreadsheet in the window.




1800H15820.25
1800H10020.25
1800H10020.25
1800H10020.25
1800H10020.25
1800H52626
1800H52626
1800H526 lbs26
1800H526 LBS26
1800H526Lbs26
1800H526 lbs.26
1800H526 LBS.26
1800H526lbs26
1800H148 LBS13.2
1800H148 lbs.13.2
1800H148 lb13.2
1800H469lb.26
1800H469lb.26
1800H148lb13.2

<tbody>
</tbody><colgroup><col span="4"></colgroup>
 
Upvote 0
Ok I am really sorry for not being consistent in my explanations. I realize that I put row in the last message. All the values would need to be in the same COLUMN that they are currently in and I would just need to remove the text (periods and letters) and leave just the numbers.
 
Upvote 0
TayoMayo,

Sample raw data in worksheet Sheet1:


Excel 2007
ABCD
11800H15820.25
21800H10020.25
31800H10020.25
41800H10020.25
51800H10020.25
61800H52626
71800H52626
81800H526 lbs26
91800H526 LBS26
101800H526Lbs26
111800H526 lbs.26
121800H526 LBS.26
131800H526lbs26
141800H148 LBS13.2
151800H148 lbs.13.2
161800H148 lb13.2
171800H469lb.26
181800H469lb.26
191800H148lb13.2
20
Sheet1


After the macro with a function (using an array in memory):


Excel 2007
ABCD
11800H15820.25
21800H10020.25
31800H10020.25
41800H10020.25
51800H10020.25
61800H52626
71800H52626
81800H52626
91800H52626
101800H52626
111800H52626
121800H52626
131800H52626
141800H14813.2
151800H14813.2
161800H14813.2
171800H46926
181800H46926
191800H14813.2
20
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.

Code: 
Sub ExtractNumbers()
' hiker95, 07/28/2014, ME794797
Dim c As Variant, i As Long
c = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
For i = 1 To UBound(c, 1)
  c(i, 1) = TextNum(c(i, 1), 0)
Next i
Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row) = c
End Sub
Function TextNum(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
  TextNum = .Replace(txt, "")
End With
End Function

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 macro.
 
Upvote 0
Here is another macro you can try also...
Code: 
Sub LeaveNumbersOnly()
  Dim LastRow As Long, UnusedCol As Long
  Const StartRow As Long = [COLOR=#FF0000][B]1[/B][/COLOR]
  Const ColNum As Long = [B][COLOR=#FF0000]3[/COLOR][/B]
  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)
    .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
NOTE: Set the starting row and column number for the first data cell containing your measurements where indicated in red above.
 
Upvote 0
TayoMayo,

With the same screenshots as my reply #6.

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).

Code: 
Sub ExtractNumbers_V2()
' hiker95 , 07/28/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))
  c.Value = RE.Replace(c.Text, "")
Next c
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_V2 macro.
 
Upvote 0
Ron Coderro,

Thank you very much.

The Original code by Ron Coderre, 04/23/2011, has been modified in my last macro ExtractNumbers_V2.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

2k05gt
  • Solved
Sum if columns match in separate sheets and total from another column
2
Replies
11
Views
339
KRice
K
C
  • Question
Removing Unneeded Characters from Cells
2
Replies
11
Views
293
Joe4
Joe4
E
  • Question
Subtotal values and then hide rows
Replies
3
Views
100
Peter_SSs
Peter_SSs
L
  • Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
335
etaf
etaf
M
  • Question
Dynamic calculations based on Dates
Replies
1
Views
124
Kaper
Kaper

Forum statistics

Threads
1,214,426
Messages
6,119,411
Members
448,894
Latest member
spenstar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top