Find and Replace exact string of text

[Serafin54]

Hello,

I have a code that replaces text with a number and it works except when a word is contained in another string. For instance, there is "Hispanic" which is a code 3 but since Hispanic also shows up in the string "Two or more Races(Not Hispanic)" which should be 7, I get "Two or more races (Not 3 or Latino)" or if it was "Hispanic/Latino" I would get "3/Latino".

Is there a way to make the text exact in the Const Ethnicity As String statement so that it looks for the entire string between the commas?

Sub Ethnic_Codes(control As IRibbonControl)
'v2.1
Const Ethnicity As String = _
"White,Black or African American,Hispanic,Asian,American Indian or Alaska Native,Native Hawaiian or Other Pacific Islander," & _
"Two or more races(Not Hispanic or Latino),Hispanic/Latino,Black/African American"


Const EthnicCodes As String = _
"1,2,3,4,5,6,7,3,2"

Dim vecEthnicity As Variant
Dim vecEthnicCodes As Variant
    Dim rStart As Range
    Set rStart = Selection
vecEthnicity = Split(Ethnicity, ",")
vecEthnicCodes = Split(EthnicCodes, ",")


For i = LBound(vecEthnicity) To UBound(vecEthnicity)
    'Set the range to suit
    rStart.Replace vecEthnicity(i), vecEthnicCodes(i)
Next

End Sub

 

[Alex Blakenburg]

Thank you for trying all the suggestions and giving great feedback.

what are things I would need to keep in mind? Like, what trumps what?

Using Replace is like using wildcards in the find part of the find & replace.
For example you are doing a find (then a replace) for
- Two or more races (Not Hispanic or Latino)
and also
- Hispanic

If you did the shorter replace first ie "Hispanic" you would change
the "Two or more races (Not Hispanic or Latino)" to being "Two or more races (Not 3 or Latino)" and then when it tries to find "Two or more races (Not Hispanic or Latino)" it would no longer exist.

The dictionary method requires an exact match.

If you want to run with the Replace method and you have a lot of data in rStart, then throwing an Array into the mix should speed it up.

Dim arrStart As Variant
arrStart = rStart.Value

For i = LBound(vecEthnicity) To UBound(vecEthnicity)
    'Set the range to suit
    'arrStart.Replace vecEthnicity(i), vecEthnicCodes(i)
    arrStart = Application.Substitute(arrStart, vecEthnicity(i), vecEthnicCodes(i))
Next

rStart.Value = arrStart

 

[bebo021999]

Thanks. I figured my original code had to do with the order of the names. If I added more ethnicity naming conventions to it, what are things I would need to keep in mind? Like, what trumps what?

If text string is in this order:

Two or more races(Not Hispanic or Latino),
Hispanic/Latino
Hispanic

After 1st replace: Two or more races(Not Hispanic or Latino) will be replaced by 7
there is no more "Not Hispanic or Latino" exists in that string

Also: After 2nd replace: Hispanic/Latino will be replaced by 3
there is no more "Hispanic/Latino " in string

And 3rd replace: Hispanic.

 

[Serafin54]

Thank you for trying all the suggestions and giving great feedback.

Using Replace is like using wildcards in the find part of the find & replace.
For example you are doing a find (then a replace) for
- Two or more races (Not Hispanic or Latino)
and also
- Hispanic

If you did the shorter replace first ie "Hispanic" you would change
the "Two or more races (Not Hispanic or Latino)" to being "Two or more races (Not 3 or Latino)" and then when it tries to find "Two or more races (Not Hispanic or Latino)" it would no longer exist.

The dictionary method requires an exact match.

No I believe i do like the dictionary method best as though it isn't the fastest method, I appear to be able to append to the list rather than having to logically determine order. However, just so i was able to keep myself a bit more focused on knowing what is there and what may need to be added at a later time, I indexed the dictionary based on Ethnic grouping. This should still work and not affect the code in any way from running in the future, should it?

Sub Ethnic_Codes(control As IRibbonControl)
    'v2.1
    Const Ethnicity As String = _
    "White,White not of Hispanic Origin," & _
    "Black or African American,Black/African American," & _
    "Hispanic or Latino,Hispanic,Hispanic/Latino," & _
    "Asian," & _
    "American Indian or Alaska Native,Am. Indian or Alaskan Native," & _
    "Native Hawaiian or Other Pacific Islander,Hawaiian or Pacific Islander," & _
    "Two or more races (Not Hispanic or Latino),Two or More Races," & _
    "Not Provided,Unspecified"

  
  
    Const EthnicCodes As String = _
    "1,1," & _
    "2,2," & _
    "3,3,3," & _
    "4," & _
    "5,5," & _
    "6,6," & _
    "7,7," & _
    ","
  
    Dim vecEthnicity As Variant
    Dim vecEthnicCodes As Variant
    Dim rStart As Range
    Dim arrStart As Variant
    Dim dictPhrase As Object
    Dim i As Long
  
    Set rStart = Selection
    arrStart = rStart.Value
  
    vecEthnicity = Split(Ethnicity, ",")
    vecEthnicCodes = Split(EthnicCodes, ",")
  
    Set dictPhrase = CreateObject("Scripting.dictionary")
  
    ' Load details range into Dictionary
    For i = 0 To UBound(vecEthnicity)
        If Not dictPhrase.exists(vecEthnicity(i)) Then
            dictPhrase(vecEthnicity(i)) = vecEthnicCodes(i)
        End If
    Next i
  
    For i = 1 To UBound(arrStart)
        If dictPhrase.exists(arrStart(i, 1)) Then
            arrStart(i, 1) = dictPhrase(arrStart(i, 1))
        End If
  
    Next i
  
    rStart = arrStart
End Sub

 

[Serafin54]

If text string is in this order:

Two or more races(Not Hispanic or Latino),
Hispanic/Latino
Hispanic

After 1st replace: Two or more races(Not Hispanic or Latino) will be replaced by 7
there is no more "Not Hispanic or Latino" exists in that string

Also: After 2nd replace: Hispanic/Latino will be replaced by 3
there is no more "Hispanic/Latino " in string

And 3rd replace: Hispanic.

ok i see. Thank you.
What if, I had "Hispanic / Latino" and also "Hispanic or Latino" would the one with a symbol come first?

 

[Alex Blakenburg]

just so i was able to keep myself a bit more focused on knowing what is there and what may need to be added at a later time, I indexed the dictionary based on Ethnic grouping. This should still work and not affect the code in any way from running in the future, should it?

The code is relying on using Split, so you can't use the same delimiter for the split that is already being used in the actual text.
The initial and subsequent code uses a "comma (,)" delimiter which is now also being used in the text.
A fairly popular delimiter is the "Pipe Symbol (|)" on my keyboard above the "\" symbol.

In the below I have modified the code to use that.
The change replaces the comma in both the 2 const lines and also in the 2 Split lines.

I would recommend replacing your 2 constant in the code with an Actual Excel Table in the Workbook. It means you don't need to go into the code to add, change, or delete, text to code mappings.

    'v2.1
    Const Ethnicity As String = _
    "White,White not of Hispanic Origin|" & _
    "Black or African American,Black/African American|" & _
    "Hispanic or Latino,Hispanic,Hispanic/Latino|" & _
    "Asian|" & _
    "American Indian or Alaska Native,Am. Indian or Alaskan Native|" & _
    "Native Hawaiian or Other Pacific Islander,Hawaiian or Pacific Islander|" & _
    "Two or more races (Not Hispanic or Latino),Two or More Races|" & _
    "Not Provided,Unspecified"

    Const EthnicCodes As String = _
    "1,1|" & _
    "2,2|" & _
    "3,3,3|" & _
    "4|" & _
    "5,5|" & _
    "6,6|" & _
    "7,7|" & _
    ","
    
    Dim vecEthnicity As Variant
    Dim vecEthnicCodes As Variant
    Dim rStart As Range
    Dim arrStart As Variant
    Dim dictPhrase As Object
    Dim i As Long
    
    Set rStart = Selection
    arrStart = rStart.Value
    
    vecEthnicity = Split(Ethnicity, "|")
    vecEthnicCodes = Split(EthnicCodes, "|")

    Set dictPhrase = CreateObject("Scripting.dictionary")
    
    ' Load details range into Dictionary
    For i = 0 To UBound(vecEthnicity)
        If Not dictPhrase.exists(vecEthnicity(i)) Then
            dictPhrase(vecEthnicity(i)) = vecEthnicCodes(i)
        End If
    Next i
    
    For i = 1 To UBound(arrStart)
        If dictPhrase.exists(arrStart(i, 1)) Then
            arrStart(i, 1) = dictPhrase(arrStart(i, 1))
        End If
    
    Next i
    
    rStart = arrStart
End Sub

 

[Serafin54]

The code is relying on using Split, so you can't use the same delimiter for the split that is already being used in the actual text.
The initial and subsequent code uses a "comma (,)" delimiter which is now also being used in the text.
A fairly popular delimiter is the "Pipe Symbol (|)" on my keyboard above the "\" symbol.

In the below I have modified the code to use that.
The change replaces the comma in both the 2 const lines and also in the 2 Split lines.

I would recommend replacing your 2 constant in the code with an Actual Excel Table in the Workbook. It means you don't need to go into the code to add, change, or delete, text to code mappings.

    'v2.1
    Const Ethnicity As String = _
    "White,White not of Hispanic Origin|" & _
    "Black or African American,Black/African American|" & _
    "Hispanic or Latino,Hispanic,Hispanic/Latino|" & _
    "Asian|" & _
    "American Indian or Alaska Native,Am. Indian or Alaskan Native|" & _
    "Native Hawaiian or Other Pacific Islander,Hawaiian or Pacific Islander|" & _
    "Two or more races (Not Hispanic or Latino),Two or More Races|" & _
    "Not Provided,Unspecified"

    Const EthnicCodes As String = _
    "1,1|" & _
    "2,2|" & _
    "3,3,3|" & _
    "4|" & _
    "5,5|" & _
    "6,6|" & _
    "7,7|" & _
    ","
   
    Dim vecEthnicity As Variant
    Dim vecEthnicCodes As Variant
    Dim rStart As Range
    Dim arrStart As Variant
    Dim dictPhrase As Object
    Dim i As Long
   
    Set rStart = Selection
    arrStart = rStart.Value
   
    vecEthnicity = Split(Ethnicity, "|")
    vecEthnicCodes = Split(EthnicCodes, "|")

    Set dictPhrase = CreateObject("Scripting.dictionary")
   
    ' Load details range into Dictionary
    For i = 0 To UBound(vecEthnicity)
        If Not dictPhrase.exists(vecEthnicity(i)) Then
            dictPhrase(vecEthnicity(i)) = vecEthnicCodes(i)
        End If
    Next i
   
    For i = 1 To UBound(arrStart)
        If dictPhrase.exists(arrStart(i, 1)) Then
            arrStart(i, 1) = dictPhrase(arrStart(i, 1))
        End If
   
    Next i
   
    rStart = arrStart
End Sub

Thank you and do appreciate the updated code and advice.

Unless I am not picking up what you're laying down, I cannot use a table for my constants as this is a tool in an custom UI add in that can be used on any report. We get 100s of different reports with fields such as this so it is a tool that rather than doing a find/replace or filter change, can save a ton of time by hitting a single button.

 

[johnnyL]

Thank you. This does work but there seems to be bit of a hang when running on a large range. I really appreciate the code though.

Too slow? Ok, This version should be practically instantaneous:

Sub Ethnic_Codes()
'
'v2.2
'
Const Ethnicity As String = _
"White,Black or African American,Hispanic,Asian,American Indian or Alaska Native,Native Hawaiian or Other Pacific Islander," & _
"Two or more races (Not Hispanic or Latino),Hispanic/Latino,Black/African American"
'
Const EthnicCodes As String = "1,2,3,4,5,6,7,3,2"
'
    Dim ArrayRow        As Long
    Dim EthnicRow       As Long
    Dim rStart          As Range
    Dim vecEthnicity    As Variant
    Dim vecEthnicCodes  As Variant
    Dim InputArray      As Variant
    Dim OutputArray()   As Variant
'
    Application.ScreenUpdating = False                                                  ' Turn ScreenUpdating off
    Application.Calculation = xlCalculationManual                                       ' Turn Calculations off
'
    Set rStart = Selection
'
    InputArray = Selection.Value                                                        ' Load selected values into 2D 1based InputArray
    ReDim OutputArray(1 To UBound(InputArray, 1), 1 To 1)                               ' Set # of rows for the 2D 1 based OutputArray
'
      vecEthnicity = Split(Ethnicity, ",")
    vecEthnicCodes = Split(EthnicCodes, ",")
'
    For ArrayRow = 1 To UBound(InputArray, 1)                                           ' Loop through each row of the selection
        For EthnicRow = LBound(vecEthnicity) To UBound(vecEthnicity)                    '   Loop through the values of vecEthnicity
            If InputArray(ArrayRow, 1) = vecEthnicity(EthnicRow) Then                   '       If the selection value = vecEthnicity value then ...
                OutputArray(ArrayRow, 1) = vecEthnicCodes(EthnicRow)                    '           Save the vecEthnicCode to the OutputArray
                Exit For                                                                '           Exit this For loop
            End If
        Next                                                                            '   Loop back
    Next                                                                                ' Loop back
'
    Selection.Offset(0, 1).Value = OutputArray                                          ' Display the OutputArray to the screen 1 column to the right of the selection
'
    Application.Calculation = xlCalculationAutomatic                                    ' Turn Calculations back on
    Application.ScreenUpdating = True                                                   ' Turn ScreenUpdating back on
End Sub

 

[Alex Blakenburg]

Thank you and do appreciate the updated code and advice.

just so i was able to keep myself a bit more focused on knowing what is there and what may need to be added at a later time, I indexed the dictionary based on Ethnic grouping. This should still work and not affect the code in any way from running in the future, should it?

Sorry I misunderstood what you meant with you groupings and I gather you picked up that my suggested change that you need to have a different delimiter for groups was not required. eg the original use of the comma delimiter worked fine. So you were right in saying that the laying out the data in groups but still comma separated would not affect the code.

What if, I had "Hispanic / Latino" and also "Hispanic or Latino" would the one with a symbol come first?

This comment of yours relates to using replace which I tried to explain uses a wild card find ie word or phrase in contained in another word or phrase.
In your example here "Hispanic / Latino" and also "Hispanic or Latino" the order doesn't matter because neither phrase can be found in the other.

However with these 3 "Hispanic or Latino" and "Two or More Races" and "Two or more races (Not Hispanic or Latino)"
the order does matter.

This is because the first 2 can be found in the last one.
So if you did them in the order I have them above a replace of "Hispanic or Latino" with a 3,
would turn all Hispanic or Latino exact matches into 3 but would also do a replace inside of phrases that contain it so you would get
Two or more races (Not 3)
If you then did a replace using Two or More Races with a 7, it would convert all exact matches to 7 but would also do a replace inside of phrases that contain it so you would get 7 (Not 3)

So the order matters and you want to do the longer phrases first, since by being longer they can be "part" of a shorter word or phrase.