How to replace second occurrence of a character in string

[#1]

Hi ,

I have a table called "Technology Versions". It has values like 8.0.5.0.1, 8.1.7.4.1, 11.1.0, 11.2.0.0.0 etc.

For comparing values in this table i need to have consistent data whose values can be compared. I want to remove all dots(.) except for the first occurrence. For example:

8.0.5.0.1 -> 8.0501

8.1.7.4.1 ->8.1741

11.1.0 ->11.10

11.2.0.0.0 ->11.2000

How can i achieve this?

[Clever_Anjos · Accepted solution]

=SubField(F1,'.',1) & '.' & SubField(F1,'.',2) & PurgeChar(Mid(F1,Index(F1,'.',2)),'.')

where F1 is your field should solve your needs

[avinashelite]

please find the attachment for the solution

[Clever_Anjos]

Simpler version: =SubField(F1,'.',1) & '.' & PurgeChar(Mid(F1,Index(F1,'.',1)),'.')