ARRAYFORMULA, SPLIT and cleaning text in Google Sheets

Most Sheets work starts with data that arrived from a form, an export or a copy-paste, and it is never clean. Names have extra spaces, cities and postcodes sit in one cell, numbers carry currency symbols. This lesson covers the small set of functions that fix those problems, and ARRAYFORMULA, which lets one formula handle every row at once.

ARRAYFORMULA: one formula for the whole column

In Excel you copy a formula down. In Sheets you can also write it once at the top and let it fill. =ARRAYFORMULA(B2:B*C2:C) in D2 multiplies every row of B by C and keeps working when new rows arrive from a form.

Example 1: a status column that stays blank for empty rows.

=ARRAYFORMULA(IF(A2:A="","",IF(E2:E>30,"Overdue","OK")))

The outer IF stops the formula writing OK into thousands of empty rows. Without it the sheet looks full and sorting goes wrong.

Splitting and joining text

Example 2: cell A2 holds Chennai, 600001. =SPLIT(A2,", ") returns Chennai in one cell and 600001 in the next. By default SPLIT treats each character in the separator as its own separator, so both the comma and the space split. That is fine here, but for a separator like " - " add a fourth argument of FALSE to treat it as one piece.

Going the other way, =TEXTJOIN(", ",TRUE,B2:D2) joins cells with a comma and skips blanks. =B2&" "&C2 joins a first and last name with a space.

Trimming, case and numbers stored as text

=TRIM(A2) removes leading, trailing and doubled spaces. =PROPER(A2) gives Title Case; =UPPER and =LOWER do what they say. Example 3: " john SMITH " becomes John Smith with =PROPER(TRIM(A2)).

A number stored as text sits to the left of its cell and is ignored by SUM. Example 4: A2 holds Rs 1,250. =VALUE(REGEXREPLACE(A2,"[^0-9.]","")) strips everything except digits and the decimal point and returns 1250. For a simple case like "1250", =VALUE(A2) is enough.

Dates typed as text behave the same way. =DATEVALUE(A2) converts recognised formats, and the Data menu's Trim whitespace cleanup handles a whole range without a formula.

Common mistakes and a practice habit

• Writing a full-column ARRAYFORMULA with no blank check, then wondering why the sheet has a thousand rows of OK.
• Splitting on a comma when some values contain commas. Check a sample first.
• Pasting values over the ARRAYFORMULA cell, which deletes it for the whole column.

Export any contact list or copy a table from a web page into a sheet. Clean it in a new tab using only formulas: split names, trim spaces, fix the case and convert every number. Then confirm SUM on the number column matches a calculator. Doing this twice makes the functions automatic.