On Fri, 19 Oct 2012 13:53:09 +0000, DustyT wrote:
I've got a spreadsheet with 600,000 email addresses in one column. Some
of these email addresses are in CAPS some start with a Capital letter.
What I want to do is to identify ALL of the rows that contain a capital
letter with (eg) "yes" in column C.
That way I can sort by this column, remove the No's, convert the
remainder to lowercase and import back into the CRM system. I don't
want to convert everything in the database as I'll then have to
re-import all 600,000 records - not a small job!
Any help would be appreciated - thanks.
I interpret your request as wanting to change ANY capital letter in the email address to lower case; not just the one's that have a capital letter as the first character.
I think this could be done mose easily by a macro.
The following macro assumes your 600,000 or so email addresses are in column A. It looks at each email address. If there are any capital letters, it converts it to lower case and also writes it out sequentially in column C. So if you just select the addresses in column C, you should have just the one's that need to be re-imported.
To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.
=================================
Option Explicit
Option Compare Binary
Sub FindCaps()
Dim v As Variant, vRes() As String
Dim i As Long, j As Long
Dim c As Range
Dim rSrc As Range, rDest As Range
Set rSrc = Range("A1", Cells(Rows.count, "A").End(xlUp))
Set rDest = Range("C1")
v = rSrc
ReDim vRes(1 To UBound(v, 1), 1 To 1)
j = 1
For i = LBound(v, 1) To UBound(v, 1)
If v(i, 1) Like "*[A-Z]*" Then
vRes(j, 1) = LCase(v(i, 1))
j = j + 1
End If
Next i
With rDest
.EntireColumn.Clear
.Resize(rowsize:=UBound(vRes)) = vRes
End With
End Sub
=====================================