VBA Replace Function not working as expected

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Replace Function not working as expected

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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