replace andCaptialletter 1

[ABerkowicz]

Hi - I have data for example like this:

SmithTomandJane
JonesAbbyandSam
SothSandy
TraceCasandra

What I need to do is find the true "and" and remove it. I was hoping to find "and" where next letter is capital and replace with nothing so results would be:

SmithTomJane
JonesAbbySam
SothSandy
TraceCasandra

Thoughts?

 

[SkipVought]

Hi,

Is this a ont-time effort or will it be periodic?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

past this into a MODULE (alt+F11 toggles between the sheet and VB Editor)

Function Rem_and(strS As String)
    Dim i As Integer

    For i = 1 To Len(strS)
        Select Case Mid(strS, i + 3, 1)
            Case "A" To "Z"
                If Mid(strS, i, 3) = "and" Then i = i + 3
        End Select
        Rem_and = Rem_and & Mid(strS, i, 1)
    Next
 End Function

Run like any other spreadsheet function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ABerkowicz]

one time.

OMG Skip, you are the best!!!! Thank you so very much!!!!!!!! This worked like a charm. Also thanks for the fast reply and have a great weekend!!!

Angela