Text & REGEX › PROPER, TRIM17 / 67

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreePROPERTRIM

PROPER + TRIM: tidy names for certificates

An entrepreneurship seminar committee in Semarang collects registrations through Google Forms. Participants type their names however they like: some in all caps, some with extra spaces. These names will be printed on certificates, so they must be tidy.

Task

In B2:B7, tidy the names in column A: remove extra spaces with TRIM, then capitalize the first letter of each word with PROPER. Write it in B2 and copy it down to B7.

Cells to fillB2:B7

Required functionsPROPERTRIM

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
TRIM removes spaces at the start and end of the text and leaves a single space between words.
Hint 2
PROPER capitalizes the first letter of each word and makes the rest lowercase.
Hint 3
A function can wrap another one: =PROPER(TRIM(…)).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

B2:B7

=PROPER(TRIM(A2))

Write it in B2, then fill down to B7.

Explanation

=PROPER(TRIM(A2)) turns " rina kurniawati" into "Rina Kurniawati". In Google Sheets, TRIM also collapses double spaces in the middle, so "DIMAS ADI PRATAMA" becomes "Dimas Adi Pratama". Be careful: PROPER also changes abbreviations, for example "PT" becomes "Pt". Tip: =ARRAYFORMULA(PROPER(TRIM(A2:A7))) in B2 tidies the whole column with one formula.

Starting worksheet: Sheet1
Row	A	B
1	Name on the form	Name on certificate
2	rina kurniawati
3	DIMAS ADI PRATAMA
4	siti nur HALIZA
5	ahmad fauzan
6	YOHANES bagus
7	putu ayu laksmi
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result