Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
if( (left(Comments,1)='c' or left(Comments,1)='d')
and len(Comments) = 7
and mid(Comments,2,6),
left(Comments,1) & '******') as UserID
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
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