Lesson 12: Clean a GL export: LEFT, TRIM and VALUE

Practice exercises for accountants, checked instantly in your browser.

Exports from accounting systems often arrive messy: account number and name in one cell, extra spaces in memos, and amounts stored as text that SUM ignores.

LEFT(text, n) returns the first n characters, so LEFT(A2,4) pulls a 4-digit account number. TRIM removes leading and trailing spaces and squeezes runs of spaces down to one. VALUE turns text like "12,480.00" into a real number you can total. When a table will not add up, check for text numbers first.

Worked example: =LEFT("4000-Revenue",4)
Returns "4000".

Exercises

Exercise 1

Juniper Hollow Farms' export puts number and name together in column A. In D2, extract the 4-digit account number and fill to D5.

Exercise 2

In E2, clean the memo in B2 so there are no extra spaces, then fill to E5.

Exercise 3

Amounts in column C are text. In F2, convert C2 to a real number and fill to F5.