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>
