2 Replies Latest reply: Sep 17, 2012 6:50 AM by Johannes Sunden RSS

    REPLACE function

      Hi,

       

      I was wondering if it would be possible to do the following in QV:

       

      I have a field which contains a series of letters: e.g. ABC-12345-12345

       

      I need to find the first "-" and replace it with a "*" - e.g. ABC*12345-12345

       

      The first part (e.g. ABC) is variable in length.  I'm trying to use the Subfield and Replace functions and I can't seem to get this to work.

       

      Any help would be greatly appreciated.

       

      Thanks!!

        • Re: REPLACE function
          Miguel Angel Baeyens de Arce

          Hi,

           

          Try the following:

           

          =Left('ABC-12345-12345', Index('ABC-12345-12345', '-') -1) & '*' & Mid('ABC-12345-12345', Index('ABC-12345-12345', '-') +1)
          

           

          Hope that helps.

           

          Miguel

          • Re: REPLACE function
            Johannes Sunden

            I'm pretty sure this can be done in a nicer way but it's almost 7pm so here goes:

             

            =replace(left('ABC-12345-12345',index('ABC-12345-12345','-')),'-','*')&right('ABC-12345-12345',len('ABC-12345-12345')-index('ABC-12345-12345','-'))

             

            Just replace the 'ABC-12345-12345' with your field name and it should be ok.

            What it does is basically to take the first section up until the first dash '-' and replaces the dash with a star '*'. Then it concatenates on the part on the right up until the first dash (which is now a star) by calculating the length of the string and subtracting the distance from the start until the first dash (star).