Text Functions
Conditional Alert Messages & 5-Star Rating Displays with REPT
Display conditional status badges without IF using REPT("ALERT", --(Condition)) and render 5-slot star or block rating bars REPT("*", Score) & REPT("-", 5 - Score).
Turn REPT(text, 0) = "" into a concise conditional badge generator!
Most spreadsheet users only think of REPT(text, number_times) for repeating dashes—forgetting that REPT("OVERDUE", 0) evaluates cleanly to an empty string "" while REPT("OVERDUE", 1) outputs "OVERDUE"! Coercing any logical test with double-unary -- inside REPT("OVERDUE", --(C2 > B2)) outputs your alert message when TRUE (1) and leaves the cell visually blank when FALSE (0)—without repeating empty-string "" branches across formulas!
Better still, chaining multiple REPT expressions concatenates independent audit tags into a single status cell (for example, REPT("[LATE] ", --(C2 > B2)) & REPT("[OVER BUDGET]", --(D2 > E2))), and pairing REPT("*", B3) & REPT("-", 5 - B3) renders a fixed 5-character star rating bar ("****-" for 4 out of 5)!
| Use Case | Formula Pattern | Input Example | Displayed Output |
|---|---|---|---|
| Single Conditional Alert | REPT("OVERDUE", --(Days > 30)) | Days = 42 / Days = 15 | "OVERDUE" / "" (Blank) |
| Multi-Flag Audit Tag | REPT("[LATE]", --(C2>30)) & REPT("[HIGH$]", --(D2>1000)) | Days = 42, Amt = $1,500 | "[LATE][HIGH$]" |
| 5-Slot Star Rating | REPT("*", Score) & REPT("-", 5 - Score) | Score = 4 (of 5) | "****-" |
Multi-Condition Audit Status Badge via Chained REPT
Appends "[OVERDUE]" when Days_Open B2 > 30 and "[ESCALATE]" when Amount C2 >= 1000; returns "" if neither applies.
=REPT("[OVERDUE]", --(B2 > 30)) & REPT("[ESCALATE]", --(C2 >= 1000))Fixed 5-Slot Star Rating Bar (With 0–5 Score Guard)
Validates integer score D2 in [0, 5] before repeating "*" D2 times and "-" (5 - D2) times.
=IF(OR(D2 < 0, D2 > 5, D2 <> INT(D2)), "Invalid Score (0-5)", REPT("*", D2) & REPT("-", 5 - D2))Try the example
Use the original values or change them to test your understanding.