Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 13th, 2007, 01:11 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Remove numbers from cell

I have a list of 100 (A1:A100) names followed by numbers in the same cell. I would like to create a macro that will remove the numbers but will leave the name alone.††For example:

John123 will be changed to John.
Pete12 will be changed to Pete.


Thanks for any help with this.
Reply With Quote
  #2 (permalink)  
Old January 13th, 2007, 02:07 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Eric

How familiar are you with VBA?

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old January 13th, 2007, 02:17 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

If so, the quick and dirty way would be a sub like the following...

Code:
Public Sub RemoveNumbers()
    Dim cellText As String
    Dim i As Integer

    For i = 1 To 100

        Range("A" & i).Select

        'Get the text from the cell
        cellText = Selection.Text

        'As long as there is a number on the right, chop it off
        While IsNumeric(Right(cellText, 1))
            cellText = Left(cellText, Len(cellText) - 1)
        Wend
        'Write the new text string back into the cell
        Selection.Value = cellText
    Next

End Sub
Hope that helps, I'm calling it a night...

Mike

Mike
EchoVue.com
Reply With Quote
  #4 (permalink)  
Old January 13th, 2007, 12:31 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot, that code worked perfectly.

Reply With Quote
  #5 (permalink)  
Old January 13th, 2007, 12:33 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Glad to help! Welcome to the forum.

Mike
EchoVue.com
Reply With Quote
  #6 (permalink)  
Old May 9th, 2008, 01:48 PM
Registered User
 
Join Date: May 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there

I need a similar code to this, only I need to delete numbers from the beginning of the cell, together with an underscore.

i.e. I want cell contents text: 219_ugo_italian_SW_hospital.wav
to change to: ugo_italian_SW_hospital.wav

Can you advise on what change is needed to the code above to make it work for me please?

Thanks
Claire
Reply With Quote
  #7 (permalink)  
Old May 9th, 2008, 08:39 PM
Friend of Wrox
Points: 3,044, Level: 22
Points: 3,044, Level: 22 Points: 3,044, Level: 22 Points: 3,044, Level: 22
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Sep 2005
Location: , , .
Posts: 807
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi clara

Please try the following:

Code:
Public Sub RemoveNumbers_FromLEft()
    Dim cellText As String
    Dim i As Integer

    For i = 1 To 100

        Range("A" & i).Select

        'Get the text from the cell
        cellText = Selection.Text

        'As long as there is a number on the left, chop it off
        While IsNumeric(Left(cellText, 1))
            cellText = Right(cellText, Len(cellText) - 1)
        Wend

        ' chop off the leftmost underscore
        If InStr(1, cellText, "_") Then
              cellText = Right(cellText, Len(cellText) - 1)
        End If

        'Write the new text string back into the cell
        Selection.Value = cellText
    Next

End Sub
Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #8 (permalink)  
Old May 10th, 2008, 01:26 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

claralou,

Here you go.

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

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:
Option Explicit
Sub RemoveNumbersUnderscore()
    Dim lngLastRow As Long
    Application.ScreenUpdating = False
    Range("A1").EntireColumn.Insert
    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
    With Range("A1:A" & lngLastRow)
        .FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-FIND(""_"",RC[1],1))"
        .Copy
        .Value = .Value
        .Copy Range("B1")
    End With
    Columns("A:A").Delete Shift:=xlToLeft
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Then run the "RemoveNumbersUnderscore" macro.


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.
Reply With Quote
  #9 (permalink)  
Old June 3rd, 2010, 11:23 AM
Registered User
 
Join Date: Jun 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi All,

I'm trying to carry out a similar task using Excel 2007. I have a column of numbers in the following format.

99.740% (99.875%)

I need to delete the first figure and the brackets so I end up with the bracketed figure but without the brackets. i.e. 99.875%

Can anyone help? Thanks in advance for you time!.

Subs
Reply With Quote
  #10 (permalink)  
Old June 3rd, 2010, 10:54 PM
Friend of Wrox
Points: 3,044, Level: 22
Points: 3,044, Level: 22 Points: 3,044, Level: 22 Points: 3,044, Level: 22
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Sep 2005
Location: , , .
Posts: 807
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can use the combination of Mid, Instr and Left Functions

Code:
Sub Extract_Numbers_In_Bracs()

Dim oCell As Range
Dim Sval

For Each oCell In Range("A1:A5")
    If InStr(oCell.Value, "(") And InStr(oCell.Value, "(") Then
        Sval = Mid(oCell.Value, InStr(oCell.Value, "(") + 1, Len(oCell.Value))
        oCell.Value = Left(Sval, InStr(Sval, ")") - 1)
    End If
Next oCell

End Sub
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to remove numbers in XML files using xsl srkumar XSLT 1 April 15th, 2008 07:43 AM
Compare numbers and letters in same cell EricB123 Excel VBA 1 January 21st, 2007 03:30 PM
Remove letters from numbers Corey Access 7 December 18th, 2005 09:09 PM
if the cell content is a part of another cell cont sriramus Excel VBA 1 November 15th, 2005 10:20 AM
Lose cell Text when editing cell in VSFlexGrid 6 bobcratchet VB How-To 0 July 30th, 2004 10:32 AM



All times are GMT -4. The time now is 02:46 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.