+ Reply to Thread
Results 1 to 11 of 11

replace values between specific characters

Hybrid View

hluk replace values between... 11-29-2009, 04:02 AM
mikerickson Re: replace values between... 11-29-2009, 05:10 AM
hluk Re: replace values between... 11-29-2009, 04:53 PM
mikerickson Re: replace values between... 11-29-2009, 06:01 PM
hluk Re: replace values between... 11-29-2009, 06:19 PM
mikerickson Re: replace values between... 11-29-2009, 07:54 PM
hluk Re: replace values between... 11-29-2009, 08:51 PM
hluk Re: replace values between... 11-30-2009, 03:05 PM
hluk Re: replace values between... 12-01-2009, 01:33 PM
  1. #1
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    replace values between specific characters

    hi,

    i have been trying to do the following but have not been successful. thanks in advance for helping!

    - i have lots of text strings on one worksheet
    - some text strings have <randomstring>, some even have multiple instances of <randomstring>, but others don't have a <randomstring> at all
    - what i want is remove anything within "<" and ">", including the operators "<" and ">"

    i have done this so far, with simple text functions, have each of the following lines in separate columns (text string column is "C"):
    D1=SEARCH("<",C1)
    E1=SEARCH(">",C1,D1)
    F1=REPLACE(C1,1,E1, )

    then because there might be multiple instances, i added:
    G1=SEARCH("<",F1)
    H1=SEARCH(">",F1,G1)
    I1=REPLACE(F1,1,H1, )

    problems and limitations of the above:
    - don't know how to loop to remove more than 2 instances
    - limited to removing "<" + "values within" + ">" from the beginning of the string only; don't know how to remove them if the "<randomstring>" is in the middle of the text string.

    sorry for the long explanation. thanks for the help!

    regards
    Last edited by hluk; 12-01-2009 at 05:39 PM.

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

    Re: replace values between specific charaters

    This UDF should do what you want.
    Try the formula =RemoveBrackets(C1)
    Function RemoveBrackets(aString As String) As String
        Dim i As Long, Snippets As Variant
        aString = Application.Substitute(aString, "<", ">")
        Snippets = Split(aString, ">")
        For i = 0 To UBound(Snippets) Step 2
            RemoveBrackets = RemoveBrackets & Snippets(i)
        Next i
    End Function
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: replace values between specific charaters

    thanks! the above worked perfectly.

    however, i now have to adapt this to remove anything between "<" and " " and everything within "<" and " "

    i modified the above to:

    Function RemoveBrackets(aString As String) As String
        Dim i As Long, Snippets As Variant
        aString = Application.Substitute(aString, "<", " ")
        Snippets = Split(aString, " ")
        For i = 0 To UBound(Snippets) Step 2
            RemoveBrackets = RemoveBrackets & Snippets(i)
        Next i
    End Function
    but when i ran it, it is cutting out too much. i guess because there is a "space" between the text strings, and it's spliting it?

    for example:
    <randomstring testing number 444 <randomstring user number 8394 <!code… activated account
    with my adapted code it becomes:
    testing444randomstringnumberactivated
    but what i want is:
    testing number 444 user number 8394 activated account
    effectively removing anything between "<" and "space":
    <randomstring
    <randomstring
    <!code…
    any ideas? thanks in advance!

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

    Re: replace values between specific charaters

    Function RemoveBrackets(aString As String) As String
        Dim i As Long, Snippets As Variant
        Snippets = Split(aString, " ")
        For i = 0 To UBound(Snippets) Step 2
            If Left(Snippets(i)) <> "<" Then
                RemoveBrackets = RemoveBrackets & Snippets(i)
            End If
        Next i
    End Function

  5. #5
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: replace values between specific characters

    thanks for the quick response =D

    i am getting this in Microsoft Visual Basic Editor after trying out the code:

    Compile error: Argument not optional
    Then it highlights the "Left" in the
    If Left(Snippets(i)) <> "<" Then
    string

    i am running excel 07, and i hope that doesn't make a difference? thanks again!

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

    Re: replace values between specific characters

    My mistake
    If Left(Snippets(i), 1) <> "<" Then

  7. #7
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: replace values between specific characters

    thanks again! error is gone

    but, the returned value is now #NAME? or 0?
    input string is
    <randomstring testing number 444 <randomstring user number 8394 <!code… activated account
    and formula used is:
    =RemoveBrackets(A2)
    the code i am using:
    Function RemoveBrackets(aString As String) As String
        Dim i As Long, Snippets As Variant
        Snippets = Split(aString, " ")
        For i = 0 To UBound(Snippets) Step 2
            If Left(Snippets(i), 1) <> "<" Then
                RemoveBrackets = RemoveBrackets & Snippets(i)
            End If
        Next i
    End Function

  8. #8
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: replace values between specific characters

    i tried to change the code some more... but it's not working out. arrrrr, ahahah, this coding stuff is not as easy as it seems.

  9. #9
    Registered User
    Join Date
    07-09-2008
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    91

    Re: replace values between specific characters

    is there a find/replace string that can do this?

    for example, is there a wildcard character that can be used to represent the characters within the string?

    e.g.) "<*******(space)"

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

    Re: replace values between specific characters

    You might try this
    Function RemoveBrackets(aString As String) As String
        Dim i As Long, Snippets As Variant
        Snippets = Split(aString, " ")
        For i = 0 To UBound(Snippets) 
            If Left(Snippets(i), 1) <> "<" Then
                RemoveBrackets = RemoveBrackets & Snippets(i) & " "
            End If
        Next i
        RemoveBrackets = Trim(RemoveBrackets)
    End Function
    That find/Replace might work too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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