CODEWITHSUNDEEP
vbaexcel
Evil Washing Machine
Evil Washing Machine

Reputation: 1341

Finding if a string contains members of a list of strings

For example if I have the string:

"All the apples in the world are round like oranges and strawberries, but they do not look like bananas. Watermelons on the other hand are big like pineapples. Apples are red and bananas are yellow."

I want to find and replace all the fruits in the string. I have a list:
fruit[apples, oranges, strawberries, bananas, watermelons, pineapples]
and I need to replace all of them with, say a vegetable of my choosing; the mapping is specified (e.g. 'apples' = 'cabbage') so it is a simple find and replace. Say that the string continues on and some of the fruits repeat themselves.

Is there a shorter way to do this than using 'While' loops for each list member and doing a Replace on each?

Upvotes: 1

Views: 854

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

Here is my take on your problem, Create two arrays, one is the fruits to find and one is the vegies to replace with in the same addresses.

Loop the array replacing as you go.

Sub Main()
Dim X As Long, FruitArr As Variant, VegArr As Variant, MyStr As String
FruitArr = Array("apples", "oranges", "strawberries", "bananas", "watermelons", "pineapples")
VegArr = Array("cabbages", "potatoes", "brussell sprouts", "cucumbers", "lettuces", "eggplants")
MyStr = "All the apples in the world are round like oranges and strawberries, but they do not look like bananas. watermelons on the other hand are big like pineapples. apples are red and bananas are yellow."
For X = UBound(FruitArr) To LBound(FruitArr) Step -1
    MyStr = Replace(MyStr, FruitArr(X), VegArr(X))
Next
Debug.Print MyStr
End Sub

Edit: Changed the order from lbound to ubound round the other way. This is so pineapple is tested and replaced before apple otherwise the apple portion of pinapple is converted incorrectly.

Upvotes: 3

Santosh
Santosh

Reputation: 12353

Try this

    Sub Main()

    Dim i As Integer
    Dim str As String
    Dim fruits As String
    Dim fruitArr() As String

    str = "All the apples in the world are round like oranges and strawberries, but they do not look like bananas." & _
          "Watermelons on the other hand are big like pineapples. Apples are red and bananas are yellow."

    If InStr(1, str, "apples") > 0 Then

        fruits = "apples, oranges, strawberries, bananas, watermelons, pineapples"


        fruitArr = Split(fruits, ",")

        For i = LBound(fruitArr) To UBound(fruitArr)

            If fruitArr(i) = "apples" Then
                str = Replace(str, "apples", "cabbage")
            End If

        Next
    End If

    Debug.Print str
End Sub

Upvotes: 0

Related Questions