Kition
sheetFormulas & Functions

Text Cleanup with Functions

A messy contact import tidied with TRIM, PROPER, TEXTBEFORE, TEXTAFTER, TEXTJOIN and a LET formula that rebuilds every phone number.

Text Cleanup with Functions — Sheet sample rendered by Kition

Names arrive with stray spaces and random capitals and phone numbers in six different styles. PROPER(TRIM()) fixes the names (O'Brien and accented letters included), TEXTBEFORE and TEXTAFTER split them, and one LET formula pulls the digits out of any phone style with MID over a SEQUENCE, keeps the last ten and formats them. The emails are assembled from the parts, and TEXTJOIN collects them into a single mailing-list line. Raw cells that needed trimming are shaded.

Steps

40 commands
showcase.yaml59 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Contact List Cleanup"], ["Sign-up sheet from the spring plant fair, as typed and as cleaned"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - [Name as typed, Phone as typed, Clean name, First, Last, Initials, Phone, Email]
        - ["  maya   PATEL", "(415) 555-0132", "=PROPER(TRIM(A5))", "=TEXTBEFORE(C5,\" \")", "=TEXTAFTER(C5,\" \",-1)", "=UPPER(LEFT(D5,1)&LEFT(E5,1))", "=LET(d,TEXTJOIN(\"\",TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)),1)*1,\"\")),r,RIGHT(d,10),\"(\"&LEFT(r,3)&\") \"&MID(r,4,3)&\"-\"&RIGHT(r,4))", "=LOWER(SUBSTITUTE(D5&\".\"&E5,\"'\",\"\"))&\"@fernhill.example\""]
        - ["JONAS weber ", "415.555.0178"]
        - ["sean o'brien", "415 555 0190"]
        - ["  \u00e9lodie martin", "+1 415-555-0145"]
        - ["Priya   NAIR", "4155550111"]
        - ["tom\u00e1s  ruiz", "415-555-0163"]
        - ["GRACE   ADEYEMI ", "(415)555-0127"]
        - ["li  WEI", "+1 (415) 555 0154"]
  - { command: edit.fillDown, params: { range: "C5:H12" } }
  - command: range.setValues
    params:
      range: A14
      values:
        - [Names that needed trimming, "=SUMPRODUCT(--(A5:A12<>TRIM(A5:A12)))"]
        - [Phone styles cleaned, "=COUNTA(B5:B12)"]
        - [Mailing list, "=TEXTJOIN(\"; \",TRUE,H5:H12)"]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#9d174d" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:B4", color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "C4:H4", color: "#9d174d" } }
  - { command: home.fontColor, params: { range: "A4:H4", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:H4" } }
  - { command: home.fontName, params: { range: "A5:B12", name: Courier New } }
  - { command: home.fontSize, params: { range: "A5:B12", size: 10 } }
  - { command: home.fontColor, params: { range: "A5:B12", color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A5:B12", color: "#f9fafb" } }
  - { command: home.conditionalFormat, params: { range: "A5:A12", rule: { type: expression, formula: "=A5<>TRIM(A5)", style: { fill: { fg: { Rgb: 16573676 }, pattern: Solid } } } } }
  - { command: home.bold, params: { range: "C5:C12" } }
  - { command: home.alignCenter, params: { range: "F4:F12" } }
  - { command: home.fontColor, params: { range: "H5:H12", color: "#1d4ed8" } }
  - { command: home.borders, params: { range: "A5:H12", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.borders, params: { range: "A12:H12", preset: bottom, color: "#9d174d" } }
  - { command: home.borders, params: { range: "C4:C12", preset: left, style: medium, color: "#9d174d" } }
  - { command: home.bold, params: { range: "A14:A16" } }
  - { command: home.alignLeft, params: { range: "B14:B15" } }
  - { command: home.alignLeft, params: { range: "B5:B12" } }
  - { command: home.mergeCells, params: { range: "B16:H17" } }
  - { command: home.wrapText, params: { range: "B16:H17" } }
  - { command: home.alignTop, params: { range: "A16:H17" } }
  - { command: home.italic, params: { range: "B16" } }
  - { command: home.fontColor, params: { range: "B16", color: "#1d4ed8" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 150 } }
  - { command: home.columnWidth, params: { cols: "B:B", width: 132 } }
  - { command: home.columnWidth, params: { cols: "C:C", width: 104 } }
  - { command: home.columnWidth, params: { cols: "D:E", width: 66 } }
  - { command: home.columnWidth, params: { cols: "F:F", width: 50 } }
  - { command: home.columnWidth, params: { cols: "G:G", width: 96 } }
  - { command: home.columnWidth, params: { cols: "H:H", width: 190 } }
  - { command: sheet.read, params: { range: "C5:H12" }, as: clean }
  - { command: sheet.read, params: { range: "B14:B16" }, as: summary }

Checks

6 checks on every build
  • results.clean.values = [["Maya Patel","Maya","Patel","MP","(415) 555-0132","maya.patel@fernhill.example"],["Jonas Weber","Jonas","Weber","JW","(415) 555-0178","jonas.weber@fernhill…
  • results.summary.values.0 = [7]
  • results.summary.values.1 = [8]
  • results.summary.values.2.0 = {"matches":"^maya\\.patel@fernhill\\.example; jonas.*li\\.wei@fernhill\\.example$","count":234}
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetFormulas & Functions

Dynamic Array Event Report

Three spilling summaries of a conference sign-up list, built with UNIQUE, SORT, FILTER, SORTBY, CHOOSECOLS and TAKE.

1 sheet; used range A1:I23
sheetFormulas & Functions

Exam Score Statistics

A class's test scores described with AVERAGE, MEDIAN, MODE, STDEV, QUARTILE and PERCENTILE, binned with FREQUENCY into a histogram.

1 sheet; used range A1:L28
sheetFormulas & Functions

Invoice Due-Date Tracker

Due dates that skip weekends and office holidays with WORKDAY, business days with NETWORKDAYS, and a status for every invoice.

1 sheet; used range A1:M19
sheetFormulas & Functions

Sales Commission Calculator

Quarterly payouts for eight reps with tiered marginal commission from a named LAMBDA, VLOOKUP rate bands and IFS bonuses.

1 sheet; used range A1:M13