Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    UDF - Remove characters

    If you please, I need help with a "User Defined Function" that will remove a specified character from text.

    <font color=blue> RemoveText(A1, "Y") </font color=blue> for example, would remove every occurrence of the letter "Y" from the selection. "Year after year" would become "ear after ear". If this would be simpler to do in MSWord, that would be fine.

    Assuming that such a function could be written, would <font color=blue> RemoveText(A1, " ") </font color=blue> remove all the spaces between the words?

    Thanks,
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF - Remove characters

    There me be a complexity that I'm missing, but couldn't you use the Substitute function. =SUBSTITUTE(A1,"Y","").

    Or if you want a UDF this one should work.

    Function RemoveText(thecell, remove As String) As Variant
    RemoveText = Application.WorksheetFunction.Substitute(thecell, remove, "")
    End Function


    RemoveText(A1, " ") remove all the spaces between the words?

    Yes, at least my version of removetext()

    <table border 1><td> </td><td>A</td><td>B</td><td>C</td><td>1</td><td>Year after Year</td><td>earafterear</td><td>=removetext(removetext(A1,"Y")," ")</td></table>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF - Remove characters

    Does this do what you want?

    <code>
    Option Explicit


    Public Function RemoveText(strS As String, strT As String) As String
    RemoveText = Application.WorksheetFunction.Substitute( _
    Application.WorksheetFunction.Substitute(strS, UCase(strT), ""), LCase(strT), "")
    End Function
    </code>
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF - Remove characters

    In my version (XL2K), Substitute is case sensitive. If you look at the original post, the first "Y" is uppercase and the second is lower case and your function would only remove the first.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF - Remove characters

    I am relegated to using XL2K and did not know that the function is case sensitive in "future" versions.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF - Remove characters

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Both you and Legare are apparently using Excel 2000.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: UDF - Remove characters

    Just as a further alternative:
    <pre>Function RemoveText(strInput As String, strRemove As String) As String
    RemoveText = Replace$(strInput, strRemove, "", , , vbTextCompare)
    End Function
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF - Remove characters

    Oh my. self- <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I read his post too quickly, and didn't even notice that I use a Y in both "Year"s when I tested. [big red face]

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF - Remove characters

    Thanks to each of you for your help,

    I wanted to do this using a UDF so I could see the code behind it. I envision manipulating the code later to be run as a macro [that would remove all the vowels and spaces from the selection]. I did have motive <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>. Figured if I could start with a basic UDF (with one argument), maybe I could figure out the rest. I may be back for additional help if I run into the <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> How neat it would be if the UDF could support multiple arguments:

    RemoveText(A1,("Y","E","A","I","O","U"," ")) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF - Remove characters

    You can define the function as follows:
    <code>
    Function RemoveText(strInput As String, ParamArray strRemove()) As String
    Dim itm
    RemoveText = strInput
    For Each itm In strRemove
    RemoveText = Replace(RemoveText, itm, "", , , vbTextCompare)
    Next itm
    End Function
    </code>
    and use it like this:
    <code>
    =RemoveText(A1,"A","E","I","O","U","Y")</code>

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: UDF - Remove characters

    Hans beat me to the function, but here is your basic macro:
    <pre>Sub RemoveCharacters(ParamArray characters())
    Dim char
    For Each char In characters
    Selection.Replace what:=char, Replacement:="", MatchCase:=False, lookat:=xlPart
    Next char
    End Sub
    Sub testremovevowels()
    RemoveCharacters "A", "E", "I", "O", "U"
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF - Remove characters

    Hans / Rory,

    That is awesome. As always, I appreciate the help. Now, I'm off to create some fun for the puzzle board...

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF - Remove characters

    Here's another version:
    <code>
    Function RemoveText(strInput As String, strRemove As String)
    Dim i As Integer
    RemoveText = strInput
    For i = 1 To Len(strRemove)
    RemoveText = Replace(RemoveText, Mid(strRemove, i, 1), "", , , vbTextCompare)
    Next i
    End Function
    </code>
    Use like this:
    <code>
    =RemoveText(A1,"aeiouy")</code>

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF - Remove characters

    I decided to use this latest version. I changed each instance of the word RemoveText to RemoveCharacterSet. I just ran this function through a few tests. It successfully removed any character I put inside the quotations, including punctuation and spaces.

    Believe it or not, it will also remove individual numerals if used without the quotations. That's a neat bonus.
    <pre>=RemoveCharacterSet(B4,2468)</pre>

    converted <font color=blue>123456789</font color=blue> to <font color=blue>13579</font color=blue> . The result could not be formatted (e.g. Currency), but it could be used in a calculation. Don't know that I'd ever need the function for that purpose, I just thought it was cool that it worked out that way.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •