Google Sheets SPLIT Drops Empty Fields: Preserve Their Column Positions

Write the expected field order before splitting

For this example, each input line is one simple record with four fields: user_id, name, city, and status. Place each entire line in a single cell, A2 through A4, and label A1 source_text. Leave B through E empty for the result. The commas belong inside the source cell at this stage.

An empty field still occupies a position. U1 has no name, and U3 has no city. Those are different missing facts. If an empty field is removed, the remaining values can slide into the wrong headings while still looking plausible, which makes the error harder to notice than a formula failure.

Set both optional SPLIT arguments explicitly

Enter the formula below in B2, then fill it down through B4. Put user_id, name, city, and status in B1:E1. The result from each formula expands across its row, so the destination cells must have room for all four positions.

Google documents TRUE as the default for both optional arguments. With remove_empty_text TRUE, consecutive delimiters are treated as one and the empty fragment is removed. Setting it to FALSE is the key change for this example. A one-character comma delimiter gives the same delimiter match either way, but explicit FALSE for split_by_each makes the intent clear.

B2:
=SPLIT(A2,",",FALSE,FALSE)

Expected B2:E2:
U1 | [empty] | Seoul | active

Default comparison:
=SPLIT(A2,",")
Expected fragments: U1 | Seoul | active

Verify every field against its heading

Check U1 and U3 separately because the blank appears in a different column. U1 should have an empty name and a populated city; U3 should have a populated name and an empty city. All three status values belong in column E.

For this limited input convention, three literal commas indicate four positions, including empty ones. You can inspect that with =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1. Do not use that count as a general CSV field counter: a comma inside a quoted field is data, not a separator. Review unexpected counts and blocked output cells before copying the results elsewhere.

Treat a multi-character separator as one token

Suppose another exporter uses two vertical bars as its separator and permits a single vertical bar inside a value. For A2 equal to U4||Mina|Jo||Seoul, use =SPLIT(A2,"||",FALSE,FALSE). The intended three fragments are U4, Mina|Jo, and Seoul.

If split_by_each is TRUE, the delimiter characters are considered individually. The single bar inside Mina|Jo can therefore become a split point too. Documenting the delimiter as a complete string avoids that mistake. The exporter must still have a rule for values that contain the complete delimiter; SPLIT does not add an escaping convention.

Use CSV-aware parsing for actual CSV quoting

A record such as U5,"Lee, Mina",Seoul,active contains four CSV fields, even though it contains four comma characters. A plain comma split does not understand the quoted name and can produce five fragments. Repeated double quotes and line breaks within quoted fields require additional CSV rules too.

For such files, use the spreadsheet's file-import workflow or a parser that supports the exporter's CSV dialect. Keep the source until names, empty values, and field counts agree. After a reviewed simple split, paste values into a separate output if a fixed table is needed. The Column Harbor checker can inspect UTF-8 CSV structure, but it does not run SPLIT formulas or reshape your sheet.

Related help: Why quoted CSV needs a parser instead of SPLIT