+ Reply to Thread
Results 1 to 4 of 4

elliminate commas, semicolons, colons, etc. from a string

Hybrid View

  1. #1
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    elliminate commas, semicolons, colons, etc. from a string

    If I have a string that is kept strictly within VBA that goes something like this:

    "This, here: is; my string"

    what is the most efficient way to eliminate commas, semicolons, and colons and convert it to "This here is my string"?

    I just need VBA, not a formula.

    Thanks
    You either quit or become really good at it. There are no other choices.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,667

    Re: elliminate commas, semicolons, colons, etc. from a string

    Few ways.
    e.g
    Sub ReplaceMethod()
        For Each e In Array(",", ":", ";")
            Columns(1).Replace e, "", 2
        Next
    End Sub
    
    Sub myReplaceFunc()
        MsgBox Replace (Replace (Replace ("This, here: is; my string", ",", ""), ":", ""), ";", "")
    End Sub
    
    Sub RegX()
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[,:;]"
            MsgBox .Replace("This, here: is; my string", "")
        End With
    End Sub

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,021

    Re: elliminate commas, semicolons, colons, etc. from a string

    Here's an ALL CAP version of jindon's REPLACEMETHOD :

    Option Explicit
    Function UStrip(sWord As String) As String
    'This function takes a string outputs an uppercase equivalent with
    '  all characters stripped but numbers and letters
    'Uppercase is used since it is a better function with "Find" since case becomes immaterial
        Dim x As Long
        Dim sCharacter As String
        sWord = UCase(sWord)  'Convert to Uppercase
        UStrip = "" 'Set originall as null
        For x = 1 To Len(sWord) 'Act on each letter
            sCharacter = Mid(sWord, x, 1)
            If Asc(sCharacter) >= 65 And _
                Asc(sCharacter) <= 90 Then '65-90 are ASCII "A"-"Z"
                'sCharacter is a LETTER
                UStrip = UStrip & sCharacter
            ElseIf sCharacter = " " Then
                'sCharacter is a space
                UStrip = UStrip & sCharacter
            ElseIf Asc(sCharacter) >= 48 And _
                Asc(sCharacter) <= 57 Then '48-57 are ASCII "0"-"9"
                'sCharacter is a "text-NUMBER"
                UStrip = UStrip & sCharacter
          End If
        Next x
    End Function
    
    Sub StripItOut()
    Dim A1 As String
    A1 = Sheets("Sheet1").Range("A1").Value
    Sheets("Sheet1").Range("A3").Value = UStrip(A1)
    End Sub

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: elliminate commas, semicolons, colons, etc. from a string

    Try

    Sub test()
        MsgBox StripCharacters("This, here: is; my string")
    End Sub
    
    Function StripCharacters(theString as String, Optional CharactersToStrip As String = ",;:") As String
        Dim I As Long
        StripCharacters = theString
    
        For i = 1 to Len(CharactersToStrip)
            StripCharacters = Replace(StripCharacters, Mid(CharactersToStrip, i, 1), vbNullString)
        Next i
    End Function
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Remove numerous trailing commas from text string that contains commas
    By JenSven1 in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2016, 10:19 PM
  2. Replies: 1
    Last Post: 05-23-2016, 11:05 AM
  3. Basic find and replace macro for commas and semicolons
    By Jacc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-30-2012, 11:32 AM
  4. Replies: 5
    Last Post: 09-11-2012, 08:40 AM
  5. Excel (2010) Macro to extract string between semi colons
    By merrener in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2012, 04:01 AM
  6. Difference colons vs. commas in functions
    By Micdis in forum Excel General
    Replies: 5
    Last Post: 03-16-2012, 09:31 PM
  7. help with semicolons and commas
    By madona33 in forum Excel General
    Replies: 3
    Last Post: 11-02-2011, 06:31 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1