CODEWITHSUNDEEP
vbaexcel
Sean Connecticut
Sean Connecticut

Reputation: 305

Using trim function with special characters

I have a column (A) of values that I would like to trim to the left. Each value in column A has a / in it which separates a word in German and it's English translation. For example one value would be "nein / no". (note there is a space between the two words.

What I want to do is write a trim function that starts after the space that follows the /. In othe words I want the value in my example to trim from "nein / no" to just "no". The part that confuses me is the fact that each value changes in size so I don't know how to tell excel where to trim from. My code should look something like this

For each cl in range("A1:A300"). Trim cl.value. Next cl

Upvotes: 0

Views: 624

Answers (3)

Jon Crowell
Jon Crowell

Reputation: 22340

You don't need VBA to solve this. A simple formula will suffice:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

nein / no = no

lederhosen / breeches = breeches

doppelganger / doppelganger = doppelganger

Upvotes: 1

Santosh
Santosh

Reputation: 12353

Try below code :

Dim start As Long

    For Each cl In Range("A1:A300")
        str = c1

        start = InStr(1, str, "/", vbTextCompare) + 1

        strVal = Trim(Right(str, Len(str) - start))
        msgbox strVal 
    Next cl

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

Start with something like this:

For each cl in Range("A1:A300")
    With cl
        .Value = Trim(Len(.Value) - Right(.Value, _
             Application.WorksheetFunction.Find("/", .Value)))
    End With
Next

Upvotes: 1

Related Questions