Finding and updating a character in a string

[BobChesh]

Clever People,

Been hunting through this forum but to no avaial. I am trying to change a string whcich contains one or more dashes "-" into a dash and a slant "/". My table contains the following:

12345
12345-67890
987465-65824-56987

and I want the data to update and change the second (or possibly third if there is one) "-" to a "/" i.e.

12345
12345-67890
987465-65824/56987

The length of each numeric element is not fixed so Len is out of the question (or is it?). I can do it in Excel by using "Find", but I have to do that a few times for each record and then concatenate, and it all get very confusing to export, modify, re-import and update. And there could be upwards of 4k records to look at and update some of them!!

Any help would be greatly appreciated.

 

[PHV]

As you asked in a VBA forum, here a VBA starting point:

If yourString Like "*-*-*" Then
  yourString = Left(yourString, InStrRev(yourString, "-") - 1) & "/" & Mid(yourString, InStrRev(yourString, "-") + 1)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya BobChesh . . .

Have a look at the [blue]Replace[/blue] function!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]