Results 1 to 4 of 4

VBA Replace Function not working as expected

This is a discussion on VBA Replace Function not working as expected within the Excel Questions forums, part of the Question Forums category; In VBA help, the syntax for the replace function is: Replace(expression, find, replace, [start], [count], [compare]) I tried the following ...

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    67

    Default VBA Replace Function not working as expected

    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/acc...ns/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.

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196

    Default Re: VBA Replace Function not working as expected

    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

    Code:
     
    Function midReplace(str, a, b, p)
        midReplace = Left(str, p - 1) & Replace(str, a, b, p)
    End Function
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    67

    Default Re: VBA Replace Function not working as expected

    Thanks very much for that Weaver.

  4. #4
    New Member
    Join Date
    May 2016
    Posts
    1

    Default Re: VBA Replace Function not working as expected

    Alternatively, you can use the excel substitute function:

    Code:
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com