Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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).