Truncate strings in Excel - is there a function to remove last part of string after separator

Question

Is there a smart macro in Excel (2000 upwards) to remove the last part of a string, if it has a certain separator?

If the separator is not there, the string should be preserved

For instance, for "."

 abcd.1          =>  abcd
 abcd            =>  abcd

I guess I could write something using a combination of Instr and Mid and such, but I am curious to know whether there is a smarter shorthand for it, like "Strip('abcd.1';'.')" or so.

Answer 1

I do not believe there is any function that will do this for you.

You can probably use something like this:

=LEFT(A1,IF(ISERROR(SEARCH(".",A1)),LEN(A1),SEARCH(".",A1) - 1))

This will remove all characters after the first instance of the seperator, if you want to remove from the last instance you may need to write your own function to do a search on the reversed string.

A full list of available functions can be found at:

http://www.techonthenet.com/excel/formulas/index.php

Answer 2

You can also create you own User Defined Function (UDF) for this.

Function Strip(ByVal oCell As Range, ByVal sSeperator As String) As String

    Dim iPos As Integer
    Dim sValue As String

    sValue = oCell.Value
    iPos = InStr(sValue, sSeperator)

    If (iPos <= 0) Then
        Strip = sValue
    Else
        Strip = Left(sValue, iPos - 1)
    End If

End Function

Then you can call that in your formula bar using

=Strip(A1, ".")