Tabellab

Headline Length by the Numbers: Characters with LEN, Word Counts, a Fit Check Against a Limit and a Summary of the Drafts

Lesson 1 of 20Lab

All lessons
  1. Headline Length by the Numbers: Characters with LEN, Word Counts, a Fit Check Against a Limit and a Summary of the DraftsLabFree
  2. Headline Formulas: Tagging Headlines by Type, Pooled Click-Through Rate per Formula with SUMIFS and an Index Against the AverageLab Locked
  3. Readability Scores: Words per Sentence, Syllables per Word, the Flesch Reading Ease with ROUND and a Plain-English VerdictLab Locked
  4. CTA Variants: Click Rate per Button Text, Relative Lift Against the Control, a Ranking with RANK.EQ and the Best Button with INDEX and MATCHLab Locked
  5. Message-Match Scores: Scoring Ad and Landing Page on Four Criteria, a Percentage Score, Labels with IFS and Bounce Rate per LabelLab Locked
  6. Reading a Copy Split Test: Conversion Rate per Version, Relative Lift, a Minimum-Data Rule with MIN and a Side-by-Side ChartLab Locked
  7. Swipe-File Scoring: Weighted Scores with SUMPRODUCT, a Ranking, a Keep-or-Archive Rule and a Row HighlightLab Locked
  8. Subject Line Length and Open Rate: Length Bands with IFS, Sends per Band and the Pooled Open Rate with SUMIFSLab Locked
  9. Headline Ingredients: Finding Words with SEARCH and ISNUMBER, Splitting Headlines into Two Groups and Comparing Their Pooled CTRLab Locked
  10. Measuring a Rewrite: Flesch-Kincaid Grade Before and After, the Change per Page, a Target Check and a Before-and-After ChartLab Locked
  11. CTA Variants Beyond the Click: Click Rate Against Revenue per View and Two Different Winners with INDEX and MATCHLab Locked
  12. What Weak Message Match Costs: Bands from the Match Score, Pooled Order Rate and Cost per Order per BandLab Locked
  13. Is the Lift Real? Pooled Rate, Standard Error with SQRT, the z-Score of a Copy Test and a 95% VerdictLab Locked
  14. Planning a Headline Test: Smallest Lift Worth Finding, Visitors per Version with ROUNDUP, Days Needed and a Run-or-Rethink DecisionLab Locked
  15. Headline Length and Click-Through Rate: Correlation with CORREL, a Trend Line with SLOPE, R² and a Forecast with FORECAST.LINEARLab Locked
  16. What a Winning Headline Is Worth: Extra Orders, Extra Revenue, a Haircut on the Lift and a 12-Month Value per TestLab Locked
  17. Subject Line or Body Copy? A What-If Grid of Revenue per Send with Mixed References, a Heat Map and a Target CountLab Locked
  18. Copy Quality Checks Before Upload: Character Limits with LEN, Counting Exclamation Marks, an OR Rule and Duplicate HeadlinesLab Locked
  19. Prioritising Copy Tests: Expected Extra Orders, Expected Value per Hour, a Ranking and the Hours of the Top IdeasLab Locked
  20. Final Project: The Copy Testing Scorecard with Lift, z-Score, Winner-Loser Verdicts, Wins per Asset Type, a Row Highlight and a ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why count a headline before you judge it?

A headline is the most read piece of any message. An email lives or dies by its subject line, an ad by its first line, a product page by the words above the picture. Writers rightly spend their time on what the line says. Yet a surprising share of headlines fail for a duller reason: the reader never sees all of them. An inbox on a phone shows the first few dozen characters and replaces the rest with three dots. A search result trims a long title. A button wraps onto two lines and pushes the price out of view.

That makes length the first check in copywriting, before taste and before testing. It is also the easiest check to automate. If you keep your drafts in a spreadsheet, three small formulas tell you for every line how long it is, how many words it has and whether it fits the space it is written for. This free lesson shows you how to build that check in Excel, step by step, with the same data as the interactive lab above.

The example is Quillhaven Paper Co., a fictional online stationery shop that is preparing the launch email for its autumn planner. The copywriter has drafted twelve subject lines. All companies, people, campaigns and numbers in this lesson are invented for learning, and the character limit is an illustrative house rule, not an official figure of any email service.

What a character limit really is

A character limit is the number of characters a channel reliably shows before it cuts the text off. "Characters" means everything you type: letters, digits, punctuation and spaces. The line Free shipping ends tonight has 23 letters and three spaces, so it is 26 characters long. Whether those 26 characters are fully visible depends on where the line appears.

Every channel has its own space, and most of them change over time. Teams therefore work with house limits: conservative numbers that are safe on the smallest common screen. Quillhaven uses the following ones. Treat them as an example of how a team writes its rules down, and check the current specifications of your own tools before you adopt any number.

Piece of copyQuillhaven house limit (illustrative)Why it is short
Email subject line45 charactersPhone inboxes show only the start of the line
Search ad headline30 charactersFixed slots; longer text is rejected or cut
Page title for search results60 charactersLong titles are trimmed with three dots
Button text20 charactersMust stay on one line on a small screen

Limits are moving targets. Screen sizes, apps and ad formats change, and what is cut off on one phone is shown in full on another. The durable skill is not remembering a number but keeping the number in one cell of your sheet, so that the whole check updates when the rule does.

The three building blocks: LEN, SUBSTITUTE and IF

LEN is short for length. =LEN(B4) returns the number of characters in cell B4, including spaces and punctuation. It works on text and on numbers, it needs no settings, and it is the function you will use most often when you check copy. For the first draft, Your new planner is here, it returns 24.

Excel has no function that counts words, but you can build one from LEN and a second function. SUBSTITUTE replaces one piece of text with another: =SUBSTITUTE(B4," ","") takes the text in B4 and replaces every space with nothing, which simply removes the spaces. Measure the result with LEN and compare it with the full length. The difference is the number of spaces, and a line with four spaces has five words. So the word count is =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1. For the first draft that is 24 − 20 + 1 = 5 words.

IF turns a number into a decision. It has three parts: a test, the result if the test is true and the result if it is false. =IF(C4<=$C$2,"Fits","Too long") asks whether the character count in C4 is at most the limit in C2. The dollar signs in $C$2 make the reference absolute: when you copy the formula down to row 5, C4 becomes C5, but $C$2 stays where it is. Without them, row 5 would compare its length with C3, row 6 with C4, and the results would be nonsense.

From a line of text to a decisionSubject linetext in B4CountLEN(B4) = 24Compare24 ≤ 45 in $C$2?DecideFits / Too longThe limit is typed once, in C2. Change it there and every status follows.LEN counts every character: letters, digits, spaces and punctuation.
Figure 1: The check in four steps. The first draft has 24 characters, 24 is at most 45, so its status is "Fits".

A fully worked example: twelve subject lines at Quillhaven

Here are the twelve drafts with the three calculated columns from the lab: characters, words and the status at the limit of 45 characters.

DraftSubject lineCharactersWordsStatus at 45
S-01Your new planner is here245Fits
S-02Plan the whole year on one page: meet the Linen Planner5511Too long
S-0320% off all dotted notebooks this weekend417Fits
S-04How to keep a notebook you will actually finish479Too long
S-05Back in stock: the A5 Linen Planner in forest green5110Too long
S-067 desk habits that make Monday mornings easier468Too long
S-07Free shipping ends tonight264Fits
S-08Which planner layout fits the way you work?438Fits
S-09New: refillable pens203Fits
S-10The notebook our customers reorder most often, now in three new colours7112Too long
S-11A small gift with your next order337Fits
S-12Last chance to pre-order the 2027 diary397Fits

The formulas behind the three calculated columns, written for row 4 and copied down to row 15, are:

  • Characters (C4): =LEN(B4). No dollar signs are needed, because each row measures its own subject line.
  • Words (D4): =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1. Full length, minus the length without spaces, plus one.
  • Status (E4): =IF(C4<=$C$2,"Fits","Too long"). The limit in C2 is locked with dollar signs, so every row is compared with the same cell.

Walk through a few rows to see the logic. S-01 has 24 characters and fits with room to spare. S-02 makes a clear promise, but at 55 characters the phone inbox would end it somewhere after "one page", and the product name at the end would be lost. S-06 is the instructive one: 46 characters, a single character over the limit. The check is strict on purpose. A rule that is waved through at 46 will be waved through at 48 next week. Here the fix is trivial, for example "7 desk habits that make Monday mornings easy", which saves two characters.

S-10 is the longest draft with 71 characters and 12 words. It is 26 characters over the limit, so trimming will not save it; it needs a new idea or belongs in the body of the email. S-09 is the shortest with 20 characters. It fits easily, which says nothing about whether "New: refillable pens" is the best line for a planner launch. Length is a gate, not a score.

Characters per subject line draft against the limitFitsToo longLimit of 45 charactersS-0124S-0255S-0341S-0447S-0551S-0646S-0726S-0843S-0920S-1071S-1133S-1239020406080Characters including spaces (fictional drafts)
Figure 2: Length of the twelve drafts. Green bars end at or before the dashed 45-character line and fit; peach bars cross it. 7 of 12 drafts fit.

Summarising the drafts: AVERAGE and COUNTIF

Twelve rows can still be read one by one. A hundred cannot, so the lab ends with two summary cells. AVERAGE adds the values of a range and divides by how many there are: =AVERAGE(C4:C15) returns the typical length of the drafts. COUNTIF counts the cells in a range that meet a condition: =COUNTIF(E4:E15,"Fits") counts the rows whose status is "Fits".

SummaryFormulaResult
Average characters=AVERAGE(C4:C15)41.3
Drafts that fit=COUNTIF(E4:E15,"Fits")7 of 12
Drafts that are too long=COUNTIF(E4:E15,"Too long")5 of 12
Total words in all drafts=SUM(D4:D15)91

The two results teach a small lesson in reading numbers. The average length is 41.3 characters, comfortably below the limit of 45. Someone who looked only at the average would conclude that the drafts are fine. In fact 5 of the 12 are too long. An average describes the middle of a group and hides its spread, while a limit is about each single line. For a pass-or-fail rule, count the passes; do not average the lengths.

What happens when the limit changes

Because the limit lives in C2, you can ask what-if questions by typing a new number. With a stricter limit of 40 characters only 5 drafts fit. At 50 characters 9 fit, and at 60 characters 11 of the 12 do. Not a single formula has to be touched; the status column and the count in H5 follow the cell.

That is also a practical way to decide on a house limit. If moving the limit from 45 to 50 would rescue two lines that your team likes, look at those lines on a real phone before changing the rule. If the limit you chose fails nearly every draft, either the limit is unrealistic or the writers have not been told about it. The sheet does not settle the question, but it shows you what each choice would cost.

Never type a limit, a rate or a threshold inside a formula. =IF(C4<=45,…) works today and is invisible tomorrow, when the rule changes and somebody has to find every 45 in the workbook. A labelled cell and an absolute reference cost ten seconds and save the search.

Step by step in Excel

  1. Put the drafts in one column, one subject line per cell, starting in B4. Paste them as plain text so that no line breaks or double spaces come along.
  2. Type the limit into C2 and label it in A2. Write next to it where the number comes from, for example "house rule for phone inboxes".
  3. In C4 type =LEN(B4) and press Enter. Select C4 again and double-click the small square at the bottom right corner of the cell to copy the formula down to the last draft.
  4. In D4 type =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1 and copy it down the same way.
  5. In E4 type =IF(C4<=$C$2,"Fits","Too long"). While the cursor is on C2 in the formula bar you can press F4 to add the dollar signs. Copy the formula down.
  6. In H4 type =AVERAGE(C4:C15) and in H5 =COUNTIF(E4:E15,"Fits").
  7. Test the sheet: change C2 to 50 and check that S-04 and S-06 switch to "Fits" while S-05 stays "Too long". Then set it back.

Common mistakes and how to spot them

  • Forgetting the + 1 in the word count. The difference of the two lengths is the number of spaces. If every word count looks one too small, the + 1 is missing.
  • Double or trailing spaces. Text pasted from documents often ends with a space or has two spaces in a row. LEN counts them and the word count goes up. Wrap the text in TRIM, as in =LEN(TRIM(B4)), to measure the cleaned line.
  • A typed-in limit. =IF(C4<=45,…) gives the right answer until the limit changes. Refer to $C$2.
  • A missing dollar sign. =IF(C4<=C2,…) is right in row 4 only. Copied down, it compares each length with the cell two rows above it. If the status flips for no visible reason from row 5 on, check the reference.
  • Less than instead of at most. A line with exactly 45 characters fits a 45-character limit. Use <=, not <.
  • Counting what the reader does not see. Personalisation tags such as a first-name placeholder are replaced when the email is sent. Measure a realistic example of the final line, not the template code.
  • Treating short as good. The check tells you whether a line can be read in full. Whether it deserves to be read is a question for the later lessons.

How to read the result

The status column sorts the drafts into two piles. The "Fits" pile goes on to the real work of choosing the best line. The "Too long" pile needs a decision per draft, and the character count helps you make it. A line that is one or two characters over, like S-06, is fixed by swapping a word. A line that is 6 to 10 characters over, like S-05 and S-02, can often be saved by cutting a clause. A line that is 26 over needs a new idea.

If a longer line must be used, front-load it: put the words that carry the message in the part every reader sees. "Back in stock: the A5 Linen Planner in forest green" loses only the colour when it is cut. "The notebook our customers reorder most often, now in three new colours" loses its news. You can look at exactly what a reader on a small screen would see with =LEFT(B4,$C$2), which returns the first 45 characters of the line.

A subject line is a promise. Keep it truthful: no fake "Re:" or "Fwd:", no invented deadlines, no claims the email does not keep. Send marketing email only to people who gave consent, and make it easy to unsubscribe. Honest lines also test better over time, because readers learn whether opening your emails is worth it.

Quick reference: functions in this lesson

FunctionWhat it does hereExample
LENCounts the characters of a text, spaces included=LEN(B4)
SUBSTITUTEReplaces text; with "" as replacement it removes it=SUBSTITUTE(B4," ","")
IFReturns one of two results depending on a test=IF(C4<=$C$2,"Fits","Too long")
AVERAGEMean of a range of numbers=AVERAGE(C4:C15)
COUNTIFCounts the cells that match a criterion=COUNTIF(E4:E15,"Fits")
TRIMRemoves extra spaces before measuring=LEN(TRIM(B4))
LEFTShows the first characters, as a small screen would=LEFT(B4,$C$2)

Real-world uses

  • Subject line shortlists. Paste twenty drafts, sort by status and discuss only the lines that fit. The meeting gets shorter and nobody argues for a line that would be cut off.
  • Ad uploads. Search and social ads have fixed slots per headline and description. A length column next to each text finds the rejects before the platform does. Lesson 18 builds a full pre-flight check on this idea.
  • Page titles and descriptions. A list of all page titles of a shop with a LEN column shows in a minute which titles will be trimmed in search results.
  • Translations. Translated copy is often noticeably longer than the English original. The same sheet with a column per language shows where a button or a headline will break the layout.
  • Briefs for writers. A limit that is written into the brief, with the check attached, saves a round of corrections.

What you will build in the rest of this course

Lesson 1 is free. The other nineteen labs of the course take the same twelve-row habit, one small data set and a handful of formulas, through the whole working day of someone who writes and tests copy.

Headline formulas and ingredients. You tag headlines as how-to, number list, question, benefit or curiosity lines and compare their pooled click-through rate with SUMIFS. Later you search headlines for single words with SEARCH and measure what an ingredient such as "you" is associated with.

Readability scores. You calculate the Flesch Reading Ease and the Flesch-Kincaid grade from word, sentence and syllable counts, set a plain-English target and measure a rewrite before and after.

CTA variants. You compare button texts by click rate, lift against the control and rank, then follow the same buttons to orders and revenue per view and find that the most clicked button is not always the one that earns most.

Message-match scores. You score how well a landing page repeats the promise of its ad, label each pair as strong, partial or weak and work out what weak match costs per order.

Split-test reading for copy. You read A/B tests the careful way: rates instead of counts, a minimum-data rule, the z-score as a significance check, the visitors and days a test needs before it starts, and the value of a winning headline in pounds per year.

Swipe-file scoring and planning. You score saved examples on weighted criteria with SUMPRODUCT, build a what-if grid for subject line against body copy, run a pre-flight quality check on ads, rank a backlog of test ideas by expected value per hour and finish with a yearly copy testing scorecard.

Summary

Length is the first, simplest and most often skipped check in copywriting. =LEN(B4) counts the characters of a line, =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1 counts its words, and =IF(C4<=$C$2,"Fits","Too long") compares it with a limit that lives in one cell. At Quillhaven Paper Co. 7 of 12 subject line drafts fit the illustrative limit of 45 characters, although the average length of 41.3 characters looks harmless, and one draft fails by a single character.

Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each cell as you go, and you end up with a length check that you can paste your own headlines into today.

Frequently asked questions

How do I count the characters of a headline in Excel?

Use =LEN(B4). It returns the number of characters in the cell, including spaces and punctuation. Copy the formula down to measure a whole list of headlines or subject lines at once.

How do I count words in an Excel cell?

Excel has no word-count function, so combine two: =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1. The formula counts the spaces and adds one. Wrap the text in TRIM first if it may contain double or trailing spaces.

How long should an email subject line be?

There is no single correct length, because inboxes and screens differ. Many teams set a conservative house limit so that the line is fully visible on a small phone; this lesson uses 45 characters as an illustrative example. Put the words that matter at the start and check your own limit on real devices.

Why should the character limit be in its own cell?

Limits change and differ by channel. If the limit sits in one labelled cell and the formula refers to it with an absolute reference such as $C$2, you change one number and every status updates. A limit typed into each formula has to be found and edited everywhere.

What does the dollar sign in $C$2 do?

It makes the reference absolute. When you copy a formula down, normal references such as C4 move to C5, C6 and so on, while $C$2 keeps pointing at the same cell. That is what you want for a limit or any other setting that all rows share.

Is a shorter headline always better?

No. A length check only tells you whether readers can see the whole line. Whether a short or a long line earns more opens, clicks or orders depends on the message and the audience, and the reliable way to find out is a split test of two versions.

Keep going: Copywriting by the Numbers: Headline Formulas & Length, Readability Scores, CTA Variants, Message Match & Split-Test Reading

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Headline Formulas: Tagging Headlines by Type, Pooled Click-Through Rate per Formula with SUMIFS and an Index Against the Average
  2. Readability Scores: Words per Sentence, Syllables per Word, the Flesch Reading Ease with ROUND and a Plain-English Verdict
  3. CTA Variants: Click Rate per Button Text, Relative Lift Against the Control, a Ranking with RANK.EQ and the Best Button with INDEX and MATCH
  4. Message-Match Scores: Scoring Ad and Landing Page on Four Criteria, a Percentage Score, Labels with IFS and Bounce Rate per Label
  5. Reading a Copy Split Test: Conversion Rate per Version, Relative Lift, a Minimum-Data Rule with MIN and a Side-by-Side Chart
  6. Swipe-File Scoring: Weighted Scores with SUMPRODUCT, a Ranking, a Keep-or-Archive Rule and a Row Highlight
  7. Subject Line Length and Open Rate: Length Bands with IFS, Sends per Band and the Pooled Open Rate with SUMIFS
  8. Headline Ingredients: Finding Words with SEARCH and ISNUMBER, Splitting Headlines into Two Groups and Comparing Their Pooled CTR
  9. Measuring a Rewrite: Flesch-Kincaid Grade Before and After, the Change per Page, a Target Check and a Before-and-After Chart
  10. CTA Variants Beyond the Click: Click Rate Against Revenue per View and Two Different Winners with INDEX and MATCH
  11. What Weak Message Match Costs: Bands from the Match Score, Pooled Order Rate and Cost per Order per Band
  12. Is the Lift Real? Pooled Rate, Standard Error with SQRT, the z-Score of a Copy Test and a 95% Verdict
  13. Planning a Headline Test: Smallest Lift Worth Finding, Visitors per Version with ROUNDUP, Days Needed and a Run-or-Rethink Decision
  14. Headline Length and Click-Through Rate: Correlation with CORREL, a Trend Line with SLOPE, R² and a Forecast with FORECAST.LINEAR
  15. What a Winning Headline Is Worth: Extra Orders, Extra Revenue, a Haircut on the Lift and a 12-Month Value per Test
  16. Subject Line or Body Copy? A What-If Grid of Revenue per Send with Mixed References, a Heat Map and a Target Count
  17. Copy Quality Checks Before Upload: Character Limits with LEN, Counting Exclamation Marks, an OR Rule and Duplicate Headlines
  18. Prioritising Copy Tests: Expected Extra Orders, Expected Value per Hour, a Ranking and the Hours of the Top Ideas
  19. Final Project: The Copy Testing Scorecard with Lift, z-Score, Winner-Loser Verdicts, Wins per Asset Type, a Row Highlight and a Chart

Buy course — $9.90 See the full course