Macro to delete all text from cells and leave numbers

[mrmonk21]

I need a macro to delete all text from cells and leave the numbers

Please help

 

[galt13]

Function cleannumber(messynumber As String) As String
Dim placeholder As String
Dim counter As Integer
For counter = 1 To Len(messynumber)
    If Mid(messynumber, counter, 1) Like "#" Then
    
        placeholder = placeholder + Mid(messynumber, counter, 1)
        
    End If
    
Next counter
cleannumber = placeholder
    
End Function

 

[raa130]

Hello! I've got this problem in my practice homework. The task which I highlighted in italics and bold is rather confusing. Should I use visual basic? and how do I delete the minutes bar? help will be much appreciated! thank you!

1.1 Click on cell A1. Create a New Web Query to retrieve data about currently logged-in							s
dotLRNusers (User Name and Online Time)
Use the following URL in your Querry: Who's Online
Specify Rich Text formatting only for Formatting option of the Web Querry
1.2. Record a macro that will refresh data retrieval with the web querry and will delete	<tbody> </tbody>
the text " minutes" from column B. (Column B should look like the sample picture)
Take into account that after each refresh the number of on-line users migth be different.
Create a button control to activate your macro (see the picture of the button)
Place your button under the picture of the sample button>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

<tbody>
</tbody>

OK...Here's what to do

• Select the workbook you want to contain the program
• ALT+F11...to open the VBA editor
• Insert.Module...to include a new general module in that workbook
• Tools.References
...Find one of the Microsoft VBScript Regular Expressions resources and check it, then click: OK
(I used: Microsoft VBScript Regular Expressions 5.5)
• Copy the below code and paste it into the new module:

Public Function PullOnly(strSrc As String, CharType As String)
Dim RE As RegExp
Dim regexpPattern As String
Set RE = New RegExp
CharType = LCase(CharType)
Select Case CharType
    Case Is = "digits":
        regexpPattern = "\D"
    Case Is = "letters":
        regexpPattern = "\d"
    Case Else:
        regexpPattern = ""
End Select
RE.Pattern = regexpPattern
RE.Global = True
PullOnly = RE.Replace(strSrc, "")
End Function
 
Sub LeaveNumbers()
Dim cCell As Range
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & PullOnly(cCell.Text, "digits")
    End If
Next cCell
End Sub

• Select the data range to be impacted in your worksheet
• ALT-F8...to see the list of available macros
• Select: LeaveNumbers
• Click: Run

Is that something you can work with?

 

[CCAmy]

This modification to the macro I posted previously will remove everything except digits and colons...

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If Not IsNumeric(Mid(CellVal, Z, 1)) And Mid(CellVal, Z, 1) <> ":" Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

I need to remove all text except dashes (-) and thought this script should work fine if I just replace the ":" with "-". It didn't work at all...didn't do anything. However, your earlier posted script to remove all text worked fine.

 

[kylefoxaustin]

OK...Here's what to do

• Select the workbook you want to contain the program
• ALT+F11...to open the VBA editor
• Insert.Module...to include a new general module in that workbook
• Tools.References
...Find one of the Microsoft VBScript Regular Expressions resources and check it, then click: OK
(I used: Microsoft VBScript Regular Expressions 5.5)
• Copy the below code and paste it into the new module:

Public Function PullOnly(strSrc As String, CharType As String)
Dim RE As RegExp
Dim regexpPattern As String
Set RE = New RegExp
CharType = LCase(CharType)
Select Case CharType
    Case Is = "digits":
        regexpPattern = "\D"
    Case Is = "letters":
        regexpPattern = "\d"
    Case Else:
        regexpPattern = ""
End Select
RE.Pattern = regexpPattern
RE.Global = True
PullOnly = RE.Replace(strSrc, "")
End Function
 
Sub LeaveNumbers()
Dim cCell As Range
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & PullOnly(cCell.Text, "digits")
    End If
Next cCell
End Sub

• Select the data range to be impacted in your worksheet
• ALT-F8...to see the list of available macros
• Select: LeaveNumbers
• Click: Run

Is that something you can work with?

WORKED LIKE A CHARM!!! came across this, desperately needed a solution. THANK YOU for building this. many thanks

 

[kylefoxaustin]

WORKED LIKE A CHARM!!! came across this, desperately needed a solution. THANK YOU for building this. many thanks

okay, realized one thing... it doesn't work with decimals. it removes the decimal point and puts the characters together. would be great if it could detect decimal numbers and keep them intact

 

[Manev]

excel 2007

I have numbers , text and minus signs in the cells and want to remove the text, but not the minus sign before the numbers. The above macro removes the minus signs unfortunately. Any ideas? Thanks

 

[Rick Rothstein]

I have numbers , text and minus signs in the cells and want to remove the text, but not the minus sign before the numbers. The above macro removes the minus signs unfortunately. Any ideas? Thanks

We have plenty of ideas, but I think you need to tell us what your mixed text/number cells look like and what you want after the code runs first. The routines earlier in the thread removed all the text leaving only numbers with no delimiters, so something like this...

abc12kljf 34dlfk567j;lkj8

would come out as this after the code runs...

12345678

It is unclear what you want when you keep the minus signs. For example, what should this look like after the code processes it...

abc-12def gh34hij-567k8l-9mn

Also, are your numbers alway whole numbers or could they have decimal points in some of them. If so, what should be done with the decimal point... delete it or keep it?

 

[Manev]

The macro removes the letters GBP but also removes the - sign before the numbers, which I would like to remain.

-4.20 GBP

10.12 GBP

11.98 GBP

0.93 GBP

9.42 GBP

11.86 GBP

3.70 GBP

11.86 GBP

14.78 GBP

9.76 GBP

-8.66 GBP

10.10 GBP

-3.56 GBP

5.55 GBP

<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody> </tbody>

 

[Manev]

The macro removes the text GBP which is fine but it also removes the minus sign before the numbers.

eg: -4.90 GBP should read as -4.90

 

[Rick Rothstein]

The macro removes the letters GBP but also removes the - sign before the numbers, which I would like to remain.

-4.20 GBP

10.12 GBP

11.98 GBP

0.93 GBP

9.42 GBP

11.86 GBP

3.70 GBP

11.86 GBP

14.78 GBP

9.76 GBP

-8.66 GBP

10.10 GBP

-3.56 GBP

5.55 GBP

<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody> </tbody>

If that is what your data looks like, you do not need a macro... simply select the column or columns with that data, press CTRL+H to bring up Excel's Replace dialog box, type a space followed by an asterisk in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the checkbox labeled "Match entire cell contents" is not checked, then click the "Replace All" button.