Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Capitalize Names (first And Last Name)

I'm doing an Excel sheet for names, phone numbers...

I'd like cell A1 & B1 to capitalize the the first and last name.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Capitalize Names (first, Middle And Last)
I have a routine where I and others enter employee names. It takes whatever case is inputted, and formats it to Capitialize the first name, Middle Initital, and Lastname. However, my routine is flawed slightly and I was wondering if there was a way to fix it.

The flaw is in the names such as McSomething, or MacDonald, McMasters.. the routine will Capitalize the first and last names, put a comma and even put a period if there is a middle initial (or not if there is no middle initial), but I can't figure out how to identify if its a Mc or a Mac or any other of those surnames. The routine will force it to Mcsomething or Macdonald ... so.. any ideas ?? It won't capitalize (in this case) the S in Mcsomething.

Public Sub StandardizeName()

Dim FN, MI, NOFI, NOLI, FirstName, LastName, First, Middle, Lastn As String

'Standardizes all name inputs - due to user input error
FN = TextBox3.Value
Lastn = TextBox1.Value
MI = TextBox4.Value

View Replies!   View Related
Capitalize First Letter Of Each Sentence
Is it possible in Excel to capitalize just the first letter of sentence?

Something from this:

"Capital first letter only. After Period Should be capitalized only The First Letter"

to this:

"Capital first letter only. After period should be capitalized only the first letter"

View Replies!   View Related
VBA Modified To Capitalize First Letter
i found this VBA but i need it to change it for more columns not just column 3 ("C") i also need D F G J T W. how can this formula be modified?

View Replies!   View Related
Create Array Of File Names/sheet Names
Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

View Replies!   View Related
Looking Names In A List With Names Written Differently And With Duplicates
I am using Excel 2003 and Windows XP.

I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:

Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.

What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.

I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was

=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)

So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.

My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.

Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?

View Replies!   View Related
Folder Names Instead Of File Names/macro
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.

Sub test()
With Application.FileSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Cells(i, 1) = "No files Found"
End If
End With
End Sub

View Replies!   View Related
Pulling Out Single Names From A String Of Names
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

View Replies!   View Related
Replace Bad Names From A List Of Good Names
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?

The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.

View Replies!   View Related
Compare 1st X Letters Of Names To Other Names
Here's what I'm trying to do:

In a spreadsheet I have a series of names with associated data, for instance: ...

View Replies!   View Related
Create A List Of Unique Names From A List Of Multiple Names
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.

What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.

The code I have so far is below:

Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant

In my mind it should check the names in the unique list against the imported list and add any missing names.

View Replies!   View Related
Swapping Last Names And First Names
I have a column which contains names e.g

Mr Derek Harold JONES

I want last name first,then space,the other names. This is because I am using VLOOKUP and the sheet I am looking up has the names in this format.

View Replies!   View Related
File Names :: File Renaming Each With The Names I Have On Another List
I have a task I would like some assistance with…

I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.

I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?

View Replies!   View Related
Names Appear More/less Than Others
Column A contains a list of names, Column B contains scores. I'd like Column C to display - say - the average of all B scores for each A name.

Should be rather simple, but I can't figure out what formula to input into C - particularly since some names appear more/less than others.

I imagine there is a way to do this using a conditional formula, but I can't seem to figure it out...


View Replies!   View Related
Using Range Names
I have in column B a link to raw data on another sheet within the same workbook that returns the same of a segment (ie "MSEC"). In column A I have SUMIF statements that total up the volume associated with each segment (ie "MSEC") that is shown in the report. Each SUMIF total is named (through Insert/Name) something that includes the segment name (ie "MSEC_TA"). In column V I want to offer a share number which shows the percentage of the total (ie "MSEC_TA) that each item is. So, I want a formula that allows me to say "E6/the text in B6 & "_TA". Since the text in B6 is "MSEC", that would give me the formula of E6/MSEC_TA, which is the named range which holds the total.

View Replies!   View Related
Re-using Query Names
how I would be able to weed out and remove previous references to query names.

For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?

View Replies!   View Related
Defining Names
Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?

View Replies!   View Related
Fix Names With Code
I have a spread sheet where I've got to change the names, eg The name Paul Dechristian with this formula: .......

View Replies!   View Related
Search The Names
I am trying to write a macro which will look at a search term entered into cell C1, then if there is a defined Name in the search range (AM1:DA5000) which matches then then name will be outputted to listbox 1.

eg if cell c1 = Out
then listbox 1 would return the following names from the above range:

I have the following code, which will only return the first instance it finds:

Public Sub findnames()Dim c As Range, rngDim n As NameActiveSheet.ListBox1.Clearmystr = [c1].Value**** ****Set rng = Range("AM1:DA5000")************For Each c In rng****************For Each n In ActiveWorkbook.Names********************If InStr(c, mystr) > 0 Then************************************ActiveSheet.ListBox1.AddItem c****************** ******************************************** End If****************Next n************Next c********End Sub

View Replies!   View Related
Names In A Column
I have a list of names in a column.. in another column i want a list of a website URL, forward slash the name for example:

Column B:

Column C:

i want the 'C' column to automatically complete when a name is entered in B
i tried[url]

but didnt work

View Replies!   View Related
Similar Names ....
As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".

I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.

HOpefully it is clear as shown below.......

As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".

I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.

HOpefully it is clear as shown below.......

View Replies!   View Related
Need 2 VBA Codes For Names
I have names in the following format in Column A: e.g.

Mr. Doe SmithSmith DoeJr. Doe SmithJr. Smith DoeMiss Abc XyzMrs Abc XyzXyz Abc

I want a VBA code that would turn all different formats of names listed above as:

Doe Smith

Abc Xyz

I also want another code that would turn above formats as follows: (by the last name)

View Replies!   View Related
Worksheet Tab Names
Is there a way of linking the worksheet tab names to a cell in the spreadsheet

View Replies!   View Related
ColorIndex Names
I know that there are ColorIndex numbers associated with Excel colors. Does anyone know if there is a list of ColorIndex names like Red, Green, etc.?

View Replies!   View Related
Delete All Names
What does he means by then use /rnr??? I tried to write /rnr in the microsoft excel menu or help key and pressed entered but got the error "separator or menu key must be one character"....

Sub DeleteAllNames()
Dim name As name
For Each name In Names
Next name
End Sub

View Replies!   View Related
Find Several Names At Once
I have a list of 900 names in excel and I need to find certain individuals by last name. I can get a list of each person I need to find...lets say 20 last names. These names are in the 900 but I would need to use the find feature and look one up at a time and this will take forever.

Let’s say sheet one has the 900 names and sheet two has the 20 names. Can I find the 20 names in sheet one quickly or do I have to use the find feature to search one person at a time on the first sheet with 900 names?

View Replies!   View Related
Macro Allow Me To See The Names
i am having trouble with a macro of sorts that will allow me to see the names (B5) Create them it as a title in "sheet2" and reference the corresponding values into a table form also in sheet 2 under it which has been tablulated below for ease of explanation "i hope".

Basicly the names in cells B & C need to be tabulated in "sheet2" as shown in columns E,F,G. at present i was doing this by Ctrl and then paste into "sheet2" but now data increasing into thousands per title and taking hours (fingers tired) ...

View Replies!   View Related
Only List Names That Don't Have A Value Next To Them
With the following list on Sheet1, on Sheet2 I want list only the Names that don't have a value next to them:

Abigail 6
Alina 24
Andrew 5

That sounds so easy to explain, but I am baffled as to how you can do it with a standard if function without getting the following type of result (this list could be up to 200 names - I only want ones that don't have a populated field next to them):

Aaron True
Alexander True
Allan True

Can this be done with a groovy IF and INDEX formula, or is VBA (of which I have very limited skills) the only way to go?

View Replies!   View Related
Get Difference In Corresponding To Names
In the below table, I was trying to get the difference in ColB corresponding to Names in ColA..

ColA ColB ColC ABC 28 1 MNO 12 1 ABC 27 1 ABC 26 2 ABC 24 1 ABC 23
XYZ 16 3 MNO 11 1 MNO 10 1 MNO 9 -1 MNO 10
XYZ 13

View Replies!   View Related
Fill Tab Names
I have 100 tabs numbered 1-100. I was wondering if there is anyway to add a tab in the middle and then fill the numbers in somehow.

View Replies!   View Related
Extract Names And #N/A From Each Other
In sheet 1i have in which Column A i have names and #N/A listed as below:


Kamlesh MoreSohel Khan#N/A#N/A#N/AAvadhut Shide#N/ARajesh KumarSahil JoshiSriram V.Sachin DeshpandeNaveen kumarBabli Bunty#N/APravin KumarManoj Joshi#N/A#N/APravin Vishwanath #N/AJohnson Sudhir khanna

Now i want to extract names and #N/A from each other and want to get it printed in sheet 2 column A & B respectively using any formulae or fumctions.

View Replies!   View Related
Add Names To Blocks
In a worksheet I have (not equal) blocks of data. In column A there is the projectnumber, in columns B-K there are data. Projects are devided by an empty row. All rows in some block have in column A the same value (projectnumber).

What I need is the following: I would like to add names to that blocks. The name must be equal to the projectnumber.

View Replies!   View Related
Merging First And Last Names
I have an excel sheet with first and last name and I have another column called username.

The username will be "firstnamelastname" all lower case and no spaces.

I know I can use C1=lower(&A1""&B1)

View Replies!   View Related
Stripping First And Last Names
I have a list of names that I need to separate into FIRST and LAST names. These names are located in column A. The problem with my initial list is that some of the names have two first names (in the format of Herman & Betty Altenfelder) and some just one first name (such as Tom Frank). Could someone please help me in writing a function that will place the first name(s) in one column (such as column B) and the last name in a different column (such as column C).

View Replies!   View Related
Copy Of Names
I have many different names in column A, and many of these names are repeated several times. How can I copy only one of the names to another sheet? Maybe a very bad explanation so see below: Orginale sheet:

Comlumn A:

What I want is a sheet with onlly this:

View Replies!   View Related
Swtiching Names Around
If I have in cell A1 and its value is Smith, John.

Can I run a macro or formula that reverse them to John Smth (no comma)?

it would be preferable to have the result in the same cell else in B2 is fine!

View Replies!   View Related
Look Up Names From A Table
I have attched my workbook. As you will see I would like to use this as a template.
Worksheet 1 (fixtures) has the layout for the first half of the season all the places are numbered. Worksheet 2 (teams) has the numbers but as yet I do not know the names of the teams. Is it possible that when I receive the names of the teams I can put them in the teams worksheet and Excel change the numbers on the fixtures list to the names of the teams. I have been doing this for a couple of years now and have done all the working out with pen and paper which can take a while.

View Replies!   View Related
Look Up Sheet Names
In Column A row 4 of sheet1 I have an account name, for example Sonic, and I have a sheet named the account name, Sonic. In Column B row 4, of sheet1, I would like to return the value in Column B row 3 in sheet named Sonic, or the account named sheet.

I have about 50 different account names along Column A in sheet1, and the same account name sheets opened in the same workbook. Is there a good formula or vba code that will work for all the values in Column A of sheet1 to look for the same named sheet and return the values off of that? Note: on the account sheets in Column A it says ‘Account’ for all of them.. this wasn’t a mistype

View Replies!   View Related
Rearranging Names....
I have a list of names in the format "SURNAME, Firstname". By using left find and mid find formulas and then concatonating the result I can put the first name first and the surname after it in proper case. This works well for the most part (although if there is an easier way I'd be happy to hear it!) except for when it comes to people with double unhyphonated surnames - lets say for example Ella Van Hamburg.

The name would come through in the raw data as VAN HAMBURG, Ella When I separate the surname and change the case it says Van Hamburg But when I go to extract the first name I get the result HAMBURG, Ella And so when I concatonate: HAMBURG, Ella Van Hamburg. It works perfectly for everyone else, and with hyphonated surnames. I am then using this data in a VLOOKUP which means that the final result has to be in the leftmost column so at the moment that's just where I'm concatonating it.

View Replies!   View Related
Formula For Names With JR, SR, III,IV
I am using excel 2002 and I am creating a letter format that is populating fields from a different spreadsheet. I am using the current formula for example:


View Replies!   View Related
Delete All Names ..
I have an excel file crashing because of corrupted names.

I used the following macro:

Sub DeleteAllNames()
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm = "Print_Titles" Then Resume Next
If nm = "Print_Areas" Then Resume Next
Next nm
On Error Goto 0
End Sub

I then used the name manager to delete hidden names.

Unfortunately some of the names are corrupted...

Name Manager prompts me to change the reference style to R1C1 to rename those corrupted names... but I tried every combination to rename them, but it won't work.......

Here are examples of what I tried (as new names for corrupted names): ...

View Replies!   View Related
Abbreviating First Names
I have a column with names
such as

vincent smith

I want this to read

v smith

That is v space smith

I dont mind putting in an extra column...note no . after v

Like the last question I asked,its for vlookup purposes.

View Replies!   View Related
Split Names Using Macro
I have file that may contain about 100 or more rows depending on the request. In column A, i have several names that is seperated by comma in ever row so basically what i want to do is to remove each name and insert a new column and paste the removed name in the new column and keep doing that until only one name is left for each row. I would like the code to check every row until the last row of the file and perform the splitting method that i have mentioned above. Pls. note that some row may contain only one name in this case we don't need to copy and insert new column but if the row contains more that one name then i would like to split it as shown below. I have attached file for details. I really appreciate for your help.

Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne.........

View Replies!   View Related
Concate Variable Names Using VBA
Here is the code loop that I have so far

For Each Dn In Rng
If Not .exists(Dn.Value) Then
.Add Dn.Value, ""

End If
Next Dn
I want to declare a varaible called peg and then for each Dn assign it to peg

so in the end for each Dn I should have peg1, peg2, etc, etc. Each of these should have a default value of zero that I will later modify.

View Replies!   View Related
Extract All Sheet Names
I have a workbook with 40 or so worksheet tabs in it. How do I extract the names of these different tabs into, lets say Column A??

View Replies!   View Related
Copy Spreadsheet With Different Names
What I’m trying to do is create a Workbook that works within a folder that pulls data from other Workbooks within the same folder.

Example: Workbook name “RATES.XLS” would be the name of the main Workbook with all the information. I would like to be able to pull certain cells from the other Workbooks as they arrive in the folder over to the Rates Workbook.
Every day, two new Workbooks show up in the same folder with a name like “1Name9_MMDDYYYY” and “2Name1_MMDDYYYY”. So the only thing different everyday is the date added to the name of the file and ofcourse the information is different, but formated the same and in the same cells.

Once I get the information from the other sheets linked, I will need to figure out a way to capture the date on the same line and move the data down the sheet within the Rates.xls workbook.

View Replies!   View Related
Automatically Defining Names
If I have a column of data with a defined name is there an easy way to update this as data is added? Can this be done when you open the workbook and without? I'd assume an open event could do it but I'm having problems finding a simle solution.

View Replies!   View Related
Separating Recurring Names
Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times?

eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet.


So Gus and Mike would be listed in the other table on the other worksheet.

View Replies!   View Related
Re-Names Files In Folder
I have the below macro which had previously worked for me, but now I am getting a runtime error. I tried troubleshooting this on my own now, but am coming up with nothing.

Sub ReName_Files()
Dim rFiles As Range, rCell As Range
Dim StrNewName As String, strOld As String
Dim strPath As String
Dim wbOpen As Workbook

Application.ScreenUpdating = False

Set rFiles = Range("A1", Range("A65536").End(xlUp))
strPath = "E:FinanceUtilization Reports2008Month End"

For Each rCell In rFiles
strOld = strPath & rCell
StrNewName = rCell(1, 2)
Set wbOpen = Workbooks.Open(strOld)
wbOpen.SaveAs strPath & StrNewName
Next rCell
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Setting Variable Names In Vba
I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…



x=number of samples

This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.

View Replies!   View Related
Lookup Using Row And Column Names
I have two workbooks used for periodically updating product pricing to wholesale customers, one of which has all the product computation info and the other all the customers and products they buy. There is one main sheet in the product information book that has all the products going down the first column with a code for each item. The headings of many of its columns represent variations in packaging. The combination of product and package variations result in the customer's unique price.

I am seeking to find the intersection of product and packaging using names rather than cell numbers. Right now I am able to use VLOOKUP with the product name and named range from lookup sheet but I seem forced to use the column number rather than column heading name. As there are a lot of columns it's easy to lose track or make mistakes. Column name would be much preferred. We are still using Excel 2000.

Example of current reference:

I would like to use something other than 24 for the lookup column, like 'packagename' instead. I have reviewed the INDEX and MATCH functions but haven't figured out how to accomplish what I am after. Incidentally, 'mainlist' is a named range in a separate workbook but referencing as shown above has worked without a hitch.

View Replies!   View Related
Copyright © 2005-08, All rights reserved