How do I import names properly capitalized

B

Botteon

I'm downloading names in a .cvs file, and many don't have the first letter
capitalized, or the whole name is capitalized. This file then gets imported
into an autoresponder and shoots out emails, but the names go out as
downloaded. How can I get them properly capitalized before importing into
autoresponder. There are so many I can't manually correct.

Alan Botteon
Coastal WealthBuilders
Level 3 Director
(e-mail address removed)
www.wademontravel.com
800-891-8329
 
B

Botteon

I so appreciate your response. As I built computers I've always been so
grateful to responders on newsgroups, where I always went when problems
exceeded my grasp. I've been away from programming dabbling and working with
Excel formulas, so that I wasn't able to do anything with your answers.
Dumbly, I guess I took your second idea and put it in the excel formula bar
for column one which is first names, and naturally, it just named everyone
=Proper(B2). I wouldn't know where to put the code in your first idea. Hate
to bother you, and I can pick it up quick, but i'm dumbfounded at the
moment.
 
G

Gord Dibben

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
B

Botteon

Thanks so much, works so great I'm not even going to try and figure it out!
Appreciation to you and all who respond. Thanks
 
B

Botteon

Could you post the code for all small letters to format an email column,
thanks
 
D

Dave Peterson

Try changing one line:

From:
cell.Formula = Application.Proper(cell.Formula)

To:
cell.Formula = lcase(cell.Formula)

to make them uppercase:
cell.Formula = ucase(cell.Formula)
 
B

Botteon

Thanks so much. And in the meanwhile, I've also been advised of the 2
following macros:
Sub ConvertToLowerCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = LCase(Rng.Value)
End If
Next Rng
End Sub

Sub ConvertToProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)

End If
Next Rng
End Sub

Thanks to all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top