CODEWITHSUNDEEP
excel-formula
cooper_milton
cooper_milton

Reputation: 87

How to trim specific character from the front and back of a string in excel formulas

I want to trim '-' from the front and back of a string, not in the middle. So:

    -Some-thing- = Some-thing
    -Another = Another
    Some-thing = Some-thing
    One more- = One more
    etc.

Does the TRIM function let you do this?

Upvotes: 2

Views: 846

Answers (1)

JNevill
JNevill

Reputation: 50034

If it's always just a single hyphen that is hanging out at the end OR the beginning of the word then you could do this with some if() functionality:

=IF(LEFT(A1, 1) = "-", RIGHT(A1, LEN(A1)-1), IF(RIGHT(A1, 1)="-", LEFT(A1, LEN(A1)-1)))

If there is going to by multiple hyphens that need to be removed, I think your best bet is with a UDF:

Function TrimCharacter(inString As String, trimChar As String) As String
    For Each word In Split(inString, trimChar)
        If Len(word) > 0 Then TrimCharacter = TrimCharacter & IIf(TrimCharacter = "", word, trimChar & word)
    Next word
End Function

Just plop that into a new module in VBE, save your workbook, then you can use it in a cell formula like:

=TrimCharacter(A1, "-")

And it will trim all hyphens from the beginning and ending of the word.

Upvotes: 1

Related Questions