Find & Replace in excel

Question

I am trying to do a mass find & replace in Microsoft excel. What I want to do is anywhere that there is a period i want to remove the first letter in front of it and everything else after it.

Example: Matt RyanM.Ryan

I want to remove the 'M.Ryan' so it just says 'Matt Ryan'. Is this even possible? If so how can it be done?

Answer 1

This will process all the cells on a single sheet:

Sub noDot()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        v = r.Value
        i = InStr(v, ".")
            If v <> "" And i <> 0 Then
                arr = Split(v, ".")
                If Len(arr(0)) < 2 Then
                    r.Value = ""
                Else
                    r.Value = Left(arr(0), Len(arr(0)) - 1)
                End If
            End If
        Next r
End Sub

Answer 2

Use ?.* as the find criterion: ? for one character before the period, and * for all characters after it:

BEFORE:

AFTER:

Answer 3

Use LEFT:

=LEFT(A1,FIND(".",A1)-2)