Format Name in Cells
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Format Name in Cells
We have a column for Name. Entries are entered LastName, FirstName. Sometimes people enter ALL CAPS and we need it formated so Cap is first letter of FirstName and LastName and lowercase for everything else. I was doing it by hand but now more and more entries are coming in and it's getting to be too much of a job. Is there a formatting condition or function or something like that which will format entries in this column automatically?
Thank you,
Dax
Thank you,
Dax
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
You could run a macro. Select the cells before running it.
Code: Select all
Sub ProperCase()
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Sweetness!! Thank you!
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Oooo. One thing I notice is it reduces the case for hyphonated names. So Smith-Holly becomes Smith-holly. Can that part be fixed?
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Name in Cells
In addition to Hans's macro, you could also use the Excel function called PROPER.
Assume the names are in column A, starting at A2
In B2, enter the formula: =PROPER(A2) and autofill down.
The formula should sort out the hyphenated issue too...
Assume the names are in column A, starting at A2
In B2, enter the formula: =PROPER(A2) and autofill down.
The formula should sort out the hyphenated issue too...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
That's much more complicated. I'll get back to you later.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Name in Cells
Am I overlooking something? (Which I tend to do...so pardon me if I have)...
If you do need an automated version and not the function I referred to, you can update Hans's macro to use the 'PROPER' worksheet function.
If you do need an automated version and not the function I referred to, you can update Hans's macro to use the 'PROPER' worksheet function.
Code: Select all
Sub ProperCase()
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
cel.Value = Application.Proper(cel.Value)
Next cel
Application.ScreenUpdating = True
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Hi Rudi,
Thanks for the info. Your solution is preferred as it would make the process automatic. I've inserted a column into my worksheet for the proper-ed names and updated the VBA in my Word document which extracts data and enters it into this spreadsheet but when I do this, it overwrites the =PROPER(<cell>) function and just enters a "0".
Here's a snippet from my Word's VBA:
i=4 is the one that overwrites. Do you know how I could update this code so it leaves the function in the cell, untouched?
Thanks for the info. Your solution is preferred as it would make the process automatic. I've inserted a column into my worksheet for the proper-ed names and updated the VBA in my Word document which extracts data and enters it into this spreadsheet but when I do this, it overwrites the =PROPER(<cell>) function and just enters a "0".
Here's a snippet from my Word's VBA:
Code: Select all
StrWkBkNm = "H:\my file location\Numbers.xlsm" 'Excel spreadsheet location
StrBkMk = "bMedicaidNum,bLname,bFname,,bDOB,VOL,bIPHospital,Inits,ciCallDate,,dInitDaysAuthFrom,dInitDaysAuthThru,,,,,bTxAgencyName"
Sep = "|" ' Data Separator Character
StrXlPwd = "MyPassword" '"Password" 'Password for the Excel file
With ActiveDocument
For i = 0 To UBound(Split(StrBkMk, ","))
If i = 1 Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & ", "
ElseIf i = 2 Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
ElseIf i = 5 Then
StrData = StrData & "VOL" & Sep
ElseIf i = 6 Then
If .FormFields(Split(StrBkMk, ",")(i)).Result = "Other" Then
StrData = StrData & .FormFields("bIPHospOther").Result & Sep
Else
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
End If
ElseIf i = 9 Then
StrData = StrData & Sep
ElseIf i = 12 Then
StrData = StrData & Sep
ElseIf i = 13 Then
StrData = StrData & Sep
ElseIf i = 14 Then
StrData = StrData & Sep
ElseIf i = 15 Then
StrData = StrData & Sep
ElseIf i = 7 Then
While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
StrIn = InputBox("Please add your initials. (In CAPS please.)", "User Initials")
Wend
StrData = StrData & StrIn & Sep
ElseIf i = UBound(Split(StrBkMk, ",")) Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result
Else
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
End If
Next
End With
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Actually, come to think of it. Maybe the code could be updated to include the "PROPER" format instruction. So the bLname and bFname parts could look something like:
...but I'm sure that's probably not quite right. But maybe you see where I'm going with it. Possible?
Code: Select all
If i = 1 Then
StrData = StrData & .FormFields (Format.PROPER (Split (StrBkMd, ",") (i))) .Result & ", "
If i = 2 Then
StrData = StrData & .FormFields (Format.PROPER (Split (StrBkMd, ",") (i))) .Result & Sep
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Name in Cells
Hi Dax,
My Word VBA is not up to par to assist you. You will need to wait for someone else to assist with VBA skills in Word.
AFAIK, you cannot use the PROPER function in Word VBA as it is not part of Words object library. The PROPER function belongs to the Application.WorksheetFormula (which is part of Excel). This is obviously the part I missed and in Hans's foresight, why he did not go into using the function in the first place.
My Word VBA is not up to par to assist you. You will need to wait for someone else to assist with VBA skills in Word.
AFAIK, you cannot use the PROPER function in Word VBA as it is not part of Words object library. The PROPER function belongs to the Application.WorksheetFormula (which is part of Excel). This is obviously the part I missed and in Hans's foresight, why he did not go into using the function in the first place.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
Sorry, you've lost me completely. What are you trying to do?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Sorry about that. I thought it was simpler at first but then I realized it got quite complicated really fast. Let's try the whole explaination.
I have a Word document locked form with named formfields. In it there's a Macro which collects the data from the fields and enters them into a spreadsheet in an Excel spreadsheet. (Hans, you've helped me with most of this.) When someone fills out the form sometimes, they don't use proper case and sometimes use all caps or no caps. What I need in the spreadsheet are properly cased names.
My first question was, is there a way to update the excel spreadsheet, which Hans you provided the Macro for but I'd have to run it and it didn't do the names with Hyphens the correct way. Rudi offered another suggestion which I really like as it is automatic and takes all the work out of it for me. However, following Rudi's advice to insert another column to grab the adjascent collumn's data (the names) and format them using the =PROPER() function, when the Macro from the Word document is run, it overwrites the =PROPER() function and enters a 0.
I'm trying to get it to work without overwritting the =PROPER() function. I'd thought that possibly updating the Word's Macro might be a place to do this so that's where the code and tangential thinking came from.
Does that make sense?
Please let me know if I need to explain any part further. And thanks millions for your time!
I have a Word document locked form with named formfields. In it there's a Macro which collects the data from the fields and enters them into a spreadsheet in an Excel spreadsheet. (Hans, you've helped me with most of this.) When someone fills out the form sometimes, they don't use proper case and sometimes use all caps or no caps. What I need in the spreadsheet are properly cased names.
My first question was, is there a way to update the excel spreadsheet, which Hans you provided the Macro for but I'd have to run it and it didn't do the names with Hyphens the correct way. Rudi offered another suggestion which I really like as it is automatic and takes all the work out of it for me. However, following Rudi's advice to insert another column to grab the adjascent collumn's data (the names) and format them using the =PROPER() function, when the Macro from the Word document is run, it overwrites the =PROPER() function and enters a 0.
I'm trying to get it to work without overwritting the =PROPER() function. I'd thought that possibly updating the Word's Macro might be a place to do this so that's where the code and tangential thinking came from.
Does that make sense?
Please let me know if I need to explain any part further. And thanks millions for your time!
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Name in Cells
Did you try running the modified code I posted (using Hans's original macro) in Excel. This will simply update the data dumped into Excel via the import. Run it from Excel after the data is dumped on the sheet.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Yes, actually that does work. And it doesn't have issues with the hyphenated names. Which is exactly what I was looking for, however, you showed me that this could actually be done without me needing to select my cells and run the Macro. I didn't know about the =PROPER() function and now that looks a LOT more attractive! (I love the idea of not having to do anything! )
So that's why I'm still going on this. It seems that the "added colum for the =PROPER() function" works great if I can just get the 'data dump' to ignore that column and not overwrite the function with a Zero.
So that's why I'm still going on this. It seems that the "added colum for the =PROPER() function" works great if I can just get the 'data dump' to ignore that column and not overwrite the function with a Zero.
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
You haven't shown us the code that writes data to the worksheet, so there is no way for us to know why the formulas are overwritten.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Yes I did. That's the code I inserted earlier. Maybe I didn't explain it well enough or include enough of it. I basically included the part of it that I thought would do what we're talking about. Here's the whole thing:
Does that help? If I'm reading the code right, the data collection gathers the form field data and then gives it incremental numbers. So i=1=bMedicaidnum, i=2=bLname, i=3=bFname, and i=4 is a blank one. These corolate with the columns on the Excel worksheet. So Column 1=Medicaid Number, 2=Lastname, Firstname (2 items combined), and 4 is where the =PROPER() function lives.
Code: Select all
Sub IPLogInsert()
Dim StrBkMk As String, StrData As String, StrIn As String, Sep As String, i As Long
Dim xlApp As Object, xlWkBk As Object, xlWsh As Object, StrWkBkNm As String
Dim bStrt As Boolean, r As Long, iDataRow As Long, bFound As Boolean, StrXlPwd As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
StrWkBkNm = "H:\My File Location\Numbers.xlsm" 'Excel spreadsheet location
StrBkMk = "bMedicaidNum,bLname,bFname,,bDOB,VOL,bIPHospital,Inits,ciCallDate,,dInitDaysAuthFrom,dInitDaysAuthThru,,,,,bTxAgencyName"
Sep = "|" ' Data Separator Character
StrXlPwd = "MyPassword" '"Password" 'Password for the Excel file
With ActiveDocument
For i = 0 To UBound(Split(StrBkMk, ","))
If i = 1 Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & ", "
ElseIf i = 2 Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
ElseIf i = 5 Then
StrData = StrData & "VOL" & Sep
ElseIf i = 6 Then
If .FormFields(Split(StrBkMk, ",")(i)).Result = "Other" Then
StrData = StrData & .FormFields("bIPHospOther").Result & Sep
Else
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
End If
ElseIf i = 9 Then
StrData = StrData & Sep
ElseIf i = 12 Then
StrData = StrData & Sep
ElseIf i = 13 Then
StrData = StrData & Sep
ElseIf i = 14 Then
StrData = StrData & Sep
ElseIf i = 15 Then
StrData = StrData & Sep
ElseIf i = 7 Then
While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
StrIn = InputBox("Please add your initials. (In CAPS please.)", "User Initials")
Wend
StrData = StrData & StrIn & Sep
ElseIf i = UBound(Split(StrBkMk, ",")) Then
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result
Else
StrData = StrData & .FormFields(Split(StrBkMk, ",")(i)).Result & Sep
End If
Next
End With
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next
bStrt = False ' Flag to say whether we've started Excel, so we can close it later
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
Else
End If
bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open
With xlApp
bFound = False
For Each xlWkBk In .Workbooks
If xlWkBk.FullName = StrWkBkNm Then ' It's open
Set xlWkBk = xlWkBk
bFound = True
Exit For
End If
Next
' Not open, so open it
If bFound = False Then
If IsFileLocked(StrWkBkNm) = True Then
MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
Exit Sub
End If
Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, Password:=StrXlPwd)
If xlWkBk Is Nothing Then
MsgBox "Cannot open:" & vbCr & StrWkBkNm
Exit Sub
End If
End If
' Update the workbook
With xlWkBk
Set xlWsh = .Worksheets(1)
' Identify the worksheet to update
If ActiveDocument.FormFields("bEnT").CheckBox.Value Then
Set xlWsh = .Worksheets("EnT_2014")
Else
Set xlWsh = .Worksheets("2014")
End If
' Update the first available row in the worksheet,
' skipping over any columns for which there are no data
With xlWsh
iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row + 1 ' -4162 = xlUp
For i = 0 To UBound(Split(StrData, Sep))
If Trim(Split(StrData, Sep)(i)) <> vbNullString Then
.Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
End If
Next
End With
End With
MsgBox "Workbook updates finished.", vbOKOnly
xlApp.Visible = True
AppActivate "Microsoft Excel"
End With
'Email Function to send email
On Error Resume Next
'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
'Outlook wasn't running, start it from code
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
'Set the recipient for the new email
.To = "person1@myplace.com"
'Set the subject
.Subject = "NOTIFICATION: IP Log Submital"
'The content of the document is used as the body for the email
.Body = "Someone hit the Submit Button!"
.Send
End With
If bStarted Then
'If we started Outlook from code, then close it
oOutlookApp.Quit
End If
'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
End Sub
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
In your earlier post, you omitted the part that starts at
' Test to see if there is a copy of Microsoft Excel already running.
i.e. you left out all the Excel code. The first part doesn't have anything to do with Excel. The code that you have now posted writes a row of values to the first available (empty) row in the Excel worksheet. It's not clear where the formula should go.
Do you want ALL cells to be formatted as proper case? If so, you can change the line
(which is the line that actually writes data to a cell) to
If you want to format only one column as proper case, please indicate which column that should be.
' Test to see if there is a copy of Microsoft Excel already running.
i.e. you left out all the Excel code. The first part doesn't have anything to do with Excel. The code that you have now posted writes a row of values to the first available (empty) row in the Excel worksheet. It's not clear where the formula should go.
Do you want ALL cells to be formatted as proper case? If so, you can change the line
Code: Select all
.Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
Code: Select all
.Cells(iDataRow, i + 1).Value = Application.Proper(Application.Trim(Split(StrData, Sep)(i)))
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
Not all the cells, only the name column, which would be column #2.
It takes 2 to tango; unless you speak binary; then it takes 10.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Format Name in Cells
In that case, change the line
to
Code: Select all
.Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
Code: Select all
If i = 1 Then ' This will become column 2, so apply Proper
.Cells(iDataRow, i + 1).Value = Application.Proper(Trim(Split(StrData, Sep)(i)))
Else ' Otherwise simply add the value
.Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
End If
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 368
- Joined: 04 Jan 2012, 22:43
Re: Format Name in Cells
It throws a Compile error: Method or data member not found.
When I hit OK, the ".Proper" characters of the code are highlighted.
When I hit OK, the ".Proper" characters of the code are highlighted.
It takes 2 to tango; unless you speak binary; then it takes 10.