Here's a Function that you could put in a Standard Module:
Code:
Public Function ParseIt(strIn As String) As String'...If there is a "_" preceeded and followed by a number replace the "_" with a "."
'...so
'...Apple_orange_cherry_1_578_grape
'...Banana_orange_blueberry_2_mango
'...Apple_orange_cherry_6_478_grape
'
'...becomes...
'...Apple_orange_cherry_1.578_grape
'...Banana_orange_blueberry_2_mango
'...Apple_orange_cherry_6.478_grape
Dim MyArray() As String
Dim MyDelim As String
MyDelim = "_"
Dim x As Long
'-- Split out the incoming string
MyArray = Split(strIn, "_")
'--Now build the output string
For x = 0 To UBound(MyArray) - 1
If IsNumeric(MyArray(x)) Then
If IsNumeric(MyArray(x + 1)) Then
MyDelim = "."
Else
MyDelim = "_"
End If
End If
ParseIt = ParseIt & MyArray(x) & MyDelim
Next
'--add the last element w/o the trailing "_"
ParseIt = ParseIt & MyArray(UBound(MyArray))
End Function
You would use it like this: Afield: = ParseIt([YourField]) in a query.