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"
<[email protected] on behalf of [email protected]> 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>
