Excel: is there a way to replace a peculiar part of a cell string?

Question

I have an excel export with a column containing staff previous training sessions in a string. Example: First Name|Last Name|Training title|January 2018 to February 2018

Due to non obligatory form fields from which data was collected, many strings have no end month value, like this January 2018 to I'm trying to find a formula to find all strings ending with to (hence followed by nothing else) and delete this orphaned part of the string so it will look like January 2018.

Find and replace is useless as it also replaces the value when there's an end month/year. Substitute seems like a good way to go but I've failed trying to find the proper structure to the formula.

You could use find() if you make surte it starts its search with only the last two characters... — Solar Mike, Feb 04 '19 at 15:14

score 3 · Accepted Answer · answered Feb 04 '19 at 14:47

3

Test whether it ends in to with right, and if so then use left to omit it:

=IF(RIGHT(A1,2) = "to",LEFT(A1,LEN(A1)-2),A1)

answered Feb 04 '19 at 14:47

Scott Craner

148,073
10
49
81

With some adaptations for my french version of Excel this worked flawlessly. Thank you Scott. In case a fellow baguette comes around here with the same issue: `=SI(DROITE(A1;2)="to";GAUCHE(A1;NBCAR(A1)-2);A1)` – InspectorG34 Feb 04 '19 at 17:48

score 0 · Answer 2 · answered Feb 04 '19 at 14:57

This is with VBA. Dont forget to check for extra spaces at the end. if there are any use TRIM.

Option Explicit

Sub Remove()

    Dim Lastrow As Long, i As Long

    'Change Sheet if needed
    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To Lastrow

            If Right(.Range("A" & i).Value, 2) = "to" Then
                .Range("A" & i).Value = Left(.Range("A" & i).Value, Len(.Range("A" & i).Value) - 3)
            End If
        Next i


    End With

End Sub

I'll try this as well. My VBA skills need some training :) – InspectorG34 Feb 04 '19 at 17:49 — InspectorG34, Feb 04 '19 at 17:49

Excel: is there a way to replace a peculiar part of a cell string?

2 Answers2