Replace prepositions and articles in excel with words using a named range

i have created a sheet to paste in text and output lower, UPPER, and Proper text I have a named range of prepositions and articles capitalizerd.

I am trying to use the SUBSTITUTE formula to replace all the occurrences or words that appear in the prepositions list

so instead of

Our Breakfast Is Consistent

We get

 Our Breakfast is Consistent

so far I have this formula, but it does not work

=SUBSTITUTE(D6, TRIM(prepositions), TRIM(LOWER(prepositions))

Can anyone help. Would be very appreciated

Thanks

Andi

Solution

I would create a user defined function

   Function prep_lower(the_text)

    Dim prep_list(10, 10)

    prep_list(1, 1) = "As"
    prep_list(1, 2) = "as"

    prep_list(2, 1) = "Or"
    prep_list(2, 2) = "or"

    prep_list(3, 1) = "Is"
    prep_list(3, 2) = "is"
    'more items......


    For n = 1 To UBound(prep_list, 1)
    the_text = Application.WorksheetFunction.Substitute(the_text, Chr(32) & prep_list(n, 1) & Chr(32), Chr(32) & prep_list(n, 2) & Chr(32))
    Next 'For n = 0 To...

    prep_lower = the_text



    End Function

then enter text in, say, cell a1 and the formula =prep_lower(A1) in b1