VBA Replace Function not working as expected

[mamboq]

In VBA help, the syntax for the replace function is: Replace(expression, find, replace, [start], [count], [compare])

I tried the following (in VBA): Msgbox Replace("Green apple", "e", "a", 7)

I was expecting "Green appla" to be the returned text, but only "appla" was returned. Is this correct? I thought the start argument was the position where the function starts looking for the 'find' substring, not the position at which the returned value begins.

My example was taken from the following page: http://www.webcheatsheet.com/SQL/access_functions/replace.php

That page is about Access, but I'm sure it's the same function in Excel - in any case, the 'unexpected' result ("appla") is identical in both access and excel.

 

[Weaver]

Yes, I've come up against this myself.

The way I got round it was to write a separate function that took the 'position' argument and chopped the string up using left

Function midReplace(str, a, b, p)
    midReplace = Left(str, p - 1) & Replace(str, a, b, p)
End Function

 

[mamboq]

Thanks very much for that Weaver.

 

[pgj1nz]

Alternatively, you can use the excel substitute function:

MsgBox Application.WorksheetFunction.Substitute("Green apple", "e", "a", 3)

Note the slight differences in the functions:

REPLACE(text, old_text, new_text [, start [, count[, compare]]]) where start is the position in the expression

Application.WorksheetFunction.SUBSTITUTE(text, old_text, new_text [, instance_num]) where instance_num is the occurrence in the expression

This is why the original value of 7 had to change to 3