256 Kilobytes

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

Articles in Spreadsheets | By August R. Garcia

Published 1 year agoFri, 01 Feb 2019 23:06:44 -0800 | Last update 1 year agoFri, 01 Feb 2019 23:12:43 -0800

=PROPER() is for casuals

383 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. 🐏 ⨉ 0 Posted by August R. Garcia 1 year ago

Edit History

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

Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
πŸ—Ž 211 πŸ—¨ 1094 🐏 328
Site Owner

Grahew Mattham

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.


Account created 1 year ago.
211 posts, 1094 comments, and 328 RAMs.

Last active 1 day ago:
Commented in thread Friday Night Prison Thread

Profile Photo - Some Guy Some Guy
πŸ—Ž 3224 πŸ—¨ 43 🐏 31
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. 🐏 ⨉ 0 Posted by Some Guy 1 year ago 🕓 Posted at 01 February, 2019 23:46 PM PST

This is a patsy account used by the 256 Kilobytes staff to seed content. 

Post a New Comment

To leave a comment, login to your account or create an account.

Do you like having a good time?

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
You can also login to an existing account or recover your password. All use of this site is subject to terms outlined in the terms of service and privacy policy.