String find and replace

[#1]

Hi all,

I have a field called Comments.

Within this field, may be a UserID, which either start with a 'd' or a 'c', followed by 6 numbers. eg. c666983 or d920333 ..........

I'd like to find this userid, and replace the 6 numeric characters, with a '*', eg/ d******

This is basically all the criteria for the user id to find: 1) starting with a d or c, 2) Followed by 6 numbers.

Any ideas on how this can be achieved?

Cheers

[Gysbert_Wassenaar]

if( (left(Comments,1)='c' or left(Comments,1)='d')

          and len(Comments) = 7

          and mid(Comments,2,6),

      left(Comments,1) & '******') as UserID

[#3]

Hi Gysbert,

On the right track, but the userid can be at any location in the Comments fields.

For example, the comment could be a string made up of : kjahfskjhsakfjhkash d666288 khgaJHGJHGASF

OR c283882 kjhgasjfkhgajhs jkhgkjhSAKLJFHLKJHAFS Apples

So, what I basically need to do is strip find this UserID in this string and replace the last 6 numbers after the 'c' or 'd' with '******'

Cheers

[#4]

This is a tricky one.

I some VB script can be used to achieve the same result, but not sure if this could be used to replace the 6 numeric characters after the 'c' or 'd'

Sub runMe3()

Dim sel3 As Range

Dim c3

Set sel3 = ActiveSheet.Range("o2:o10000")

For Each c3 In sel3.Cells

    c3.Value = maskUsers(c3.Value)

    Next

End Sub

Function maskUsers(inText As String) As String

    Dim myString As String

    Dim myPattern As String

    Dim myPosition As Long

    Dim myFlag As Boolean

    myString = inText

    myPattern = "[a-dA-D]######"

    For myPosition = 1 To Len(myString) - 6

        myFlag = False

        If Mid(myString, myPosition, 7) Like myPattern Then

            ''''Pattern matched

            myFlag = True

            ''''Ignore false positive based upon preceding alphabetic

            If myPosition > 1 Then

                If Mid(myString, myPosition - 1, 1) Like "[a-zA-Z]" Then myFlag = False

            End If

            ''''Ignore false positive based upon trailing numeric

            If myPosition < Len(myString) - 7 Then

                If Mid(myString, myPosition + 7, 1) Like "[0-9]" Then myFlag = False

            End If

            ''''Mask matched pattern

            If myFlag Then

                myString = Left(myString, myPosition - 1) & "*******" & Right(myString, Len(myString) - myPosition - 6)

            End If

        End If

    Next myPosition

    maskUsers = myString

End Function