256 Kilobytes

Custom Capitalization in Google Sheets -- Handle Acronyms, Industry Terms, Etc. in Titles

Articles in Spreadsheets | By August R. Garcia

Published | Last Update

=PROPER() is for casuals

560 views, 0 RAMs, and 1 comment

If you have ever wanted title case or similar in Google Sheets, Excel, or other spreadsheet tools, you've probably seen this function, which capitalizes the first letter of all words in a string:

You can see how this is suboptimal in some cases.

How to Run a Custom Capitalization Script in Google Sheets

Create a Custom Dictionary

Go to a new tab and, in some column, create a list of terms that you want to have capitalized in a specific way. Capitalize the terms how you want them to appear. Here's what was used while writing this script:

256 Kilobytes
256Kilobytes
7-Zip
7Zip
Acer
ActionScript
Ahrefs
AIO
AlienWare
Amazon
AMD
Android
Apache
Arduino
Artisan
ASCII
Asus
Audacity
Avast
AWS
BackTrack Linux
Bash
BIOS
BlackHatWorld
Blade
Blue Yeti
Bodhi
Bodhi Linux
CPP
CentOS
CES
Chrome
ChromeBook
Chromium
CKEditor
ClearOS
CMOS
CoffeeScript
CommonJS
CompTIA
Corsair
CPanel
CPU
Craigslist
CSRF
CSS
CSV
CUDA
DB
DBrand
DDR
DDR2
DDR3
DDR4
DDS
Debian
DirectX
Discord
Divi
DLC
DNS
Docker
Dreamhost
Drupal
DSI
DynamoDB
Eclipse
ElasticSearch
Elementary OS
Eloquent
EVGA
Excel
FaceBook
Fedora
Fire Stick
FireFox
Fiverr
Galaxy
GB
GeneratePress
Gentoo
GIMP
Gmail
GNU
GoDaddy
Google Docs
Google Maps
Google Pixel
Google Sheets
GoPro
GPU
GUI
GZip
Hadoop
HHD
Horde
Hostgator
HP
HTML
iCloud
IIS
Imgur
Inkscape
Instagram
Intel
Intel Optane
IO
IP
ISP
iTunes
Java
JavaScript
JMeter
JS
JSON
Kali
KB
Kinect
Kinguin
Kodi
Krita
LAN
Laravel
Lenovo
LG
LibreOffice
Linux
Logitech
Lubuntu
M.2
Mac
MacBook
Majestic
MariaDB
MB
Microsoft
MineCraft
Mint
MongoDB
Moz
MTurk
MySQL
NAT
Netbeans
Nintendo
Notepad
NumPy
NVIDIA
Oculus
OpenJDK
OpenOffice
Optifine
Oracle
OS
Pandas
PayPal
PBN
PC
PCs
Perl
Photoshop
PHP
PHPMyAdmin
PhySx
Pinterest
POST
Postgre
Postgres
PostgreSQL
PowerPoint
PPC
ProtonMail
PS2
PS3
PS4
PSX
Python
Qubes
QuickBooks
Radeon
RAM
Rasp Pi
Rasp. Pi
Raspberry Pi
Raspbian
Razer
RDBMS
Red Hat
Reddit
REGEX
RoudeCube
RPM
Ruby
Ryzen
Samsung
SD
Seagate
SEM
SEO
Slack
SMM
Snapchat
Socialblade
Sony
Spotify
SQL
SQL Server
SQLite
SquirrelMail
SSD
SSL
Steam
StumbleUpon
SUSE
SVG
TB
TensorFlow
Thinkpad
Toshiba
Tutanota
Twitter
TypeScript
Ubuntu
Unicode
Upwork
USB
VBox
Vim
Visual Basic
VMWare
VRAM
VRChat
Walmart
WAN
Warriro Forum
WiFi
Windows
Wix
WordPress
Xbox
XenForo
xFinity
XML
XPath
Xubuntu
Yahoo
YNAB
Yoast
YouTube

Note: If you have non-alphanumeric characters in any of these terms (spaces are fine), they may have a stroke (unless you fuck with the "var re = ..." line of the function below). Specifically, characters that have special meanings in regular expressions, such as the plus symbol, may cause the script to throw an error unless you adjust the code to escape those characters (obligatory plug for this quality RegEx testing tool).

Create the Script

Go to Tools --> Script Editor, copy-paste in this function, and press Ctrl+S to save the script.

// =ghetto_hack(D3, 'Ghetto-Dictionary'!$A$2:$A$250)
function ghetto_hack(str, dict) {
  for (var iii = 0; iii < dict.length; ++iii) {
    var re = new RegExp("\\b"+dict[iii]+"\\b","gmi");
	str = str.replace(re, dict[iii]);
  } return str;
}

Run the Function

From the spreadsheet, run the formula as follows

  • =ghetto_hack(cell_with_text, range_with_custom_dictionary)
  • =ghetto_hack(A1, F1:F500)
  • =ghetto_hack(D3, 'Ghetto-Dictionary'!$A$2:$A$250)
  • If you want to run PROPER normally on everything else:
    • =ghetto_hack( PROPER(D3), 'Ghetto-Dictionary'!$A$2:$A$250)
Download more RAM. 🐏 ⨉ 0Posted by August R. Garcia 2 years ago

Edit History

• [2019-02-01 23:06 PST] August R. Garcia (2 years ago)
• [2019-02-01 23:06 PST] August R. Garcia (2 years ago)
• [2019-02-01 23:06 PST] August R. Garcia (2 years ago)
• [2019-02-01 23:06 PST] August R. Garcia (2 years ago)
• [2019-02-01 23:06 PST] August R. Garcia (2 years ago)
🕓 Posted at 01 February, 2019 23:06 PM PST

Profile Photo - August R. GarciaAugust R. GarciaLARPing as a Sysadmi...Portland, ORSite Owner

August Garcia is some guy who used to sell Viagra on the Internet. He made this website to LARP as a sysadmin while posting about garbage like user-agent spoofing, spintax, the only good keyboard, virtual assitants from Pakistan, links with the rel="nofollow" attributeproxiessin, the developer console, literally every link building method, and other junk.

Available at arg@256kilobytes.com, via Twitter, or arg.256kilobytes.com. Open to business inquiries based on availability.

Profile Photo - Some GuySome GuyBasic Subscriber

Bonus note. If the value in F2 does not contain any question marks, add a question mark at the end; otherwise, get the unmodified string in F2:

  • =if(find("?",F2), F2, F2&"?")

    Download more RAM. 🐏 ⨉ 0Posted by Some Guy 2 years ago 🕓 Posted at 01 February, 2019 23:46 PM PST

    This is a debug account.

    Post a New Comment

    Do you like having a good time?

    Register an Account

    You can also login to an existing account or reset your password. All use of this site is subject to the terms of service and privacy policy.

    Read Quality Articles

    Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

    View Articles →

    Argue with People on the Internet

    Use your account to explain why people are wrong on the Internet forum.

    View Forum →

    Vandalize the Wiki

    Or don't. I'm not your dad.

    View Wiki →

    Ask and/or Answer Questions

    If someone asks a terrible question, post a LMGTFY link.

    View Answers →

    Make Some Money

    Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

    Register an Account