String Function

[Codebug]

How can I trim a string as follows?


Dim MyText AS String

MyText = "X1123-2"




so that MyText can become "X1123"

[Stewart Ross]

How can I trim a string as follows?
MyText = "X1123-2"
so that MyText can become "X1123"

Hi Codebug. Bit more information would be helpful here...

Is the 'extra' bit always separated by a dash, and is the dash always present? If both of these are always true, you can use InStr to find the position of the dash, then Left to extract the portion of the string before the dash, or indeed both together:

Left(MyText, InStr(MyText, "-")-1)

If it is not always a dash that separates the other part, is it any punctuation character, or one of very few (such as a period '.')?

Need to know in order to make suggestions that will work always and not just sometimes. Easy to overlook the other cases - until the function fails when faced with no dash or a different separator!

-Stewart

[Codebug]

yes It is always a '-' that seperates the text, although sometimes there is no '-' presnt and need to leave the string as it is


ie.

"X1287-2" = "X1287"
"X1287" = "X1287

[Stewart Ross]

yes It is always a '-' that seperates the text, although sometimes there is no '-' presnt and need to leave the string as it is
ie.
"X1287-2" = "X1287"
"X1287" = "X1287

...in which case we need to test for the dash not being present:

Public Function BeforeDash(StringIn As String) As String
    Dim DashPos As Integer
    Const Separator = "-"
    DashPos = InStr(1, StringIn, Separator)
    If DashPos > 0 Then
        BeforeDash = Left$(StringIn, DashPos - 1)
    Else
        BeforeDash = StringIn
    End If
End Function

Place this function in a general module somewhere and refer to it as you would any other function (e.g. BeforeDash([field name])

-Stewart