CODEWITHSUNDEEP
excelstring
Brummo
Brummo

Reputation: 1040

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

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.

Upvotes: 2

Views: 54282

Answers (5)

user5248982
user5248982

Reputation:

This works for the German version of Excel.

=LINKS(A1;WENN(ISTFEHLER(SUCHEN(".";A1));LÄNGE(A1);SUCHEN(".";A1) - 1))

Upvotes: 0

John Smith
John Smith

Reputation: 1

'Use SPLIT in a function

Public Function extractName(parmField As String, Optional parmSplitChar As String = ".") As String

Dim vSplit As Variant

vSplit = Split(parmField, parmSplitChar)
extractName = vSplit(0)

End Function

Upvotes: 0

Sam
Sam

Reputation: 21

You could alternately use the Data column, click on the "text to column option", select "delimited", hit next and select the symbol of your choice to split the data(in your case, '.'). Your data should now be split in two columns. Delete the second column :)

Upvotes: 2

bendewey
bendewey

Reputation: 40235

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, ".")

Upvotes: 2

Chris
Chris

Reputation: 3517

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

Upvotes: 7

Related Questions