Names with Upper and Proper Case Words


Board Regular
Jun 29, 2018
I have a column of names. Each name has at least 2 words - the first name and the last name. Some of the names also have 1 or more middle names in addition to the the first and the last names. However, the names, i.e. the first, middle and last names are not in any particular order. But the first name is always a proper case word and the last name is always an upper case word. However, the middle names in some of the cases are proper case words and in other cases upper case words. Also, in a name where there are more than one middle names, one or two of the middle names would be in proper case and the rest in upper case. I need to arrange the names in a particular order - first name, middle name(s), last name!

For example, the name Jose Joaquin Aristizabal Murcia is currently appearing as either "ARISTIZABAL MURCIA Jose Joaquin" or "JOAQUIN ARISTIZABAL MURCIA Jose" or even "Jose Joaquin ARISTIZABAL MURCIA"! Please note that the first name is always in a Proper Case Word, the last name is always in an Upper Case Word, whereas the middle names appear both as upper case as well as proper case words. Also note that there's no particular order in which the names are entered. There are names without any middle names as well in the column. Also, there are names with more than 2 middle names with all possible combinations of positions of the middle name(s) as well as proper case and upper case words. I need to convert it to "Jose Joaquin Aristizabal Murcia".

How to do it, preferably without a macro?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...