This probably isn't helpful, but I would do this in Pandas with python:


```
import pandas as pd

def apa_book_title(row):
    titles = row['Title'].split(':', maxsplit=1)
    formatted = []
    for title in titles:
        title = title.strip()
        title = title[0].upper() + title[1:].lower()
        formatted.append(title)
    return ': '.join(formatted)

df = pd.read_excel('TitleTest.xlsx')

df['Capitalized_title'] = df.apply(apa_book_title, axis=1)
```

This outputs the following table:]
```
		Id 	Title 						Capitalized_title
0 	12334345 	THIS IS A CAPITAL TITLE: THIS IS A SUBTITLE 	This is a capital title: This is a subtitle
1 	3426t3423 	This is a Title and Lower Case title: This is ... 	This is a title and lower case title: This is ...
2 	23434564513 	This is no properly formatted 			This is no properly formatted
3 	453i9900 	this is totally lowercase: even the subtitle 	This is totally lowercase: Even the subtitle
```

Of course, this is going to ignore personal names. 

You could add SpaCy component to recognize personal names that might get a lot of them.

I would just use this formatting: 

```
def first_letter_cap(row):
    return row['Title'].title()
```
Which produces this table: 

```

	Id 		Title 						Capitalized_title 			Built_In_title
0 	12334345 	THIS IS A CAPITAL TITLE: THIS IS A SUBTITLE 	This is a capital title: This is a subtitle 	This Is A Capital Title: This Is A Subtitle
1 	3426t3423 	This is a Title and Lower Case title: This is ... 	This is a title and lower case title: This is ... 	This Is A Title And Lower Case Title: This Is ...
2 	23434564513 	This is no properly formatted 	This is no properly formatted 	This Is No Properly Formatted
3 	453i9900 	this is totally lowercase: even the subtitle 	This is totally lowercase: Even the subtitle 	This Is Totally Lowercase: Even The Subtitle
```

Though if you absolutely must have more proper APA style, then you will need something pretty sophisticated. 

-- 
Shawn Goodwin 
Metadata Control Analyst | Atla <http://www.atla.com/>
300 South Wacker Drive, Suite 2100 
Chicago, IL 60606 
 

On 6/17/19, 2:23 PM, "Code for Libraries on behalf of Mackenzie M. Salisbury" <[log in to unmask] on behalf of [log in to unmask]> wrote:

    Hi all!
    
    Does anyone know of an Excel Formula to change Title Fields into APA Format (in terms of Capitalization)? Or a way to make a batch change in another program? I've seen the one off copy and paste website, but nothing that would handle more than one title at a time.
    
    
    Best!
    
    
    Mackenzie Salisbury (she, her, hers)<https://academicguides.waldenu.edu/diversity-inclusion>
    Librarian, Digital Scholarship
    Walden University Library<https://academicguides.waldenu.edu/library?utm_source=outlook&utm_medium=email&utm_campaign=email_signature>
    <https://www.facebook.com/Walden-University-Library>
    
    <https://www.facebook.com/Walden-University-Library>
    
    <https://www.facebook.com/Walden-University-Library>[icon_facebook]<https://www.facebook.com/Walden-University-Library>  <https://www.instagram.com/walden.university.library/> [icon_instagram] <https://www.instagram.com/walden.university.library>   <https://twitter.com/WaldenULibrary> [icon_twitter] <https://twitter.com/WaldenULibrary>   <https://academicguides.waldenu.edu/library/studentnews?utm_source=outlook&utm_medium=email&utm_campaign=email_signature> [icon_libnews] <https://academicguides.waldenu.edu/library/studentnews?utm_source=outlook&utm_medium=email&utm_campaign=email_signature>