Macro to delete all text from cells and leave numbers

Sort by date Sort by votes
Code: 
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
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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!
[TABLE="width: 847"]
<tbody>[TR]
[TD]1.1 Click on cell A1. Create a New Web Query to retrieve data about currently logged-in[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]s[/TD]
[/TR]
[TR]
[TD="colspan: 4"]dotLRNusers (User Name and Online Time)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]Use the following URL in your Querry: Who's Online[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Specify Rich Text formatting only for Formatting option of the Web Querry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]1.2. Record a macro that will refresh data retrieval with the web querry and will delete[/TD]
[TD="align: left"]
clip_image005.gif

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]the text " minutes" from column B. (Column B should look like the sample picture)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Take into account that after each refresh the number of on-line users migth be different.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Create a button control to activate your macro (see the picture of the button)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Place your button under the picture of the sample button>>>>>>>>>>>>>>>>>>>>>>>>>>>>>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ron Coderre said:
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:
Code: 
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?
Click to expand...
 
Last edited:
Upvote 0
Rick Rothstein said:
This modification to the macro I posted previously will remove everything except digits and colons...

Code: 
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
Click to expand...

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.
 
Upvote 0
Ron Coderre said:
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:
Code: 
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?
Click to expand...


WORKED LIKE A CHARM!!! came across this, desperately needed a solution. THANK YOU for building this. many thanks:)
 
Upvote 0
kylefoxaustin said:
WORKED LIKE A CHARM!!! came across this, desperately needed a solution. THANK YOU for building this. many thanks:)
Click to expand...

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
 
Upvote 0
Manev said:
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
Click to expand...
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?
 
Last edited:
Upvote 0
The macro removes the letters GBP but also removes the - sign before the numbers, which I would like to remain.
[TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl24, width: 72, bgcolor: transparent"]-4.20 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]10.12 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.98 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]0.93 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]9.42 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.86 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]3.70 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.86 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]14.78 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]9.76 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]-8.66 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]10.10 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]-3.56 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]5.55 GBP[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
Manev said:
The macro removes the letters GBP but also removes the - sign before the numbers, which I would like to remain.
[TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl24, width: 72, bgcolor: transparent"]-4.20 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]10.12 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.98 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]0.93 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]9.42 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.86 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]3.70 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]11.86 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]14.78 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]9.76 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]-8.66 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]10.10 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]-3.56 GBP[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]5.55 GBP[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA macro for combining and deleting duplicate rows, and adding values from one column
Replies
0
Views
60
Darko
D
C
  • Solved
Find and Replace any values of 0 or more
Replies
8
Views
122
Fuji
F
zookeepertx
  • Solved
Trying (unsuccessfully) to pull a date from the middle of a string
2 3
Replies
23
Views
527
zookeepertx
zookeepertx
R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
314
NoSparks
N
Q
  • Question Question
VBA code to split by multiple delimiters and calculate
Replies
7
Views
203
bebo021999
B

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

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