We help IT Professionals succeed at work.

How do I remove a # from a string

upobDaPlaya
upobDaPlaya asked
on
If I want to loop thru Column A in my Excel spreadsheet and remove # from all strings found in Column A then how would I do this ?  I found a function called  Application.Substitute that may work, but I am fuzzy on how to set the range for just Column A and loop thru Column A to remove #.  In addition I am unsure if the  Application.Substitute is the best approach ?

Thoughts, suggestions ?
Comment
Watch Question

Commented:
Try macro below
Sub Del_Char()
    Set cel = ActiveSheet.Range("A:A").Find("#", LookIn:=xlValues, LookAt:=xlPart)
    Do While Not cel Is Nothing
       'cel.Value = Replace(cel.Value, "#", "")
       cel.Value = Application.Substitute(cel.Value, "#", "")
       Set cel = ActiveSheet.Range("A:A").FindNext(cel)
    Loop
End Sub

Open in new window

Freelance Web Developer
Commented:
I would simply use the Search/Replace function Ctrl+H  Search for # Replace with nothing.

In a macro:

Sub Macro1()
    Columns("A:A").Select
    Selection.Replace What:="#", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Not that this method may change items from text to number formats

example:
#125.1500
will become:
125.15


Hope this helps
Carlos RamirezFreelance Web Developer

Commented:
Typo: the "Not that this ..." section should read:

Note that this method may change items from text to number formats.

hi,

NOT for points.

Slycoder's code can be shortened slightly to prevent the sheet's cell selection changing, for example:

Sub Macro1()
    activesheet.Columns("A:A").Replace What:="#", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Open in new window


hth
Rob

Author

Commented:
Thanks for everyones input