Find rows that contain capital letter?

[DustyT]

I've got a spreadsheet with 600,000 email addresses in one column. Som
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 capita
letter with (eg) "yes" in column C.

That way I can sort by this column, remove the No's, convert th
remainder to lowercase and import back into the CRM system. I don'
want to convert everything in the database as I'll then have t
re-import all 600,000 records - not a small job!

Any help would be appreciated - thanks

 

[Claus Busch]

Hi Dusty,

Am Fri, 19 Oct 2012 13:53:09 +0000 schrieb DustyT:

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.

the formula will give you a TRUE or FALSE for the first letter of the
email address:
=AND(CODE(LEFT(B1,1))>64,CODE(LEFT(B1,1))<91)


Regards
Claus Busch

 

[Ron Rosenfeld]

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
=====================================

 

[DustyT]

the formula will give you a TRUE or FALSE for the first letter of the
email address:
=AND(CODE(LEFT(B1,1))>64,CODE(LEFT(B1,1))<91)

Hi Claus,

That's fantastic - thanks.

Dusty

 

[Spencer101]

I've got a spreadsheet with 600,000 email addresses in one column. Som
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 capita
letter with (eg) "yes" in column C.

That way I can sort by this column, remove the No's, convert th
remainder to lowercase and import back into the CRM system. I don'
want to convert everything in the database as I'll then have t
re-import all 600,000 records - not a small job!

Any help would be appreciated - thanks.

Have a look at this thread (on another forum).
Seems you could make this work for you.

http://www.excelforum.com/excel-general/656818-search-upper-case.htm