strikethrough.text

Excel formula on Windows and Mac

Strikethrough formula for Excel and Google Sheets

A formula can’t switch on strikethrough formatting, but it can build crossed-out text. Add UNICHAR(822) after every character: in Excel 365, =CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822)).

Excel 365 · 2021=CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822))
Google Sheets=REGEXREPLACE(A1,"(.)","$1"&UNICHAR(822))
Formatting insteadCtrl+5Excel. Google Sheets: Alt + Shift + 5. Mac: ⌘ + Shift + X

Updated · strikethroughtext.online

Why a formula can’t apply strikethrough

Excel and Google Sheets formulas return values: numbers, text, dates. Formatting such as bold, colours and strikethrough belongs to the cell, and no built-in function can change it. That’s why searching for a “strikethrough function” turns up nothing.

What a formula can do is return text that already contains the line. Unicode has a combining character, U+0336 combining long stroke overlay, that draws a horizontal line through whichever character comes before it. Put one after every letter and the whole word looks struck through. Its decimal code is 822, so in a formula it’s UNICHAR(822).

Excel 365 and Excel 2021

=CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822))

How it works:

  1. SEQUENCE(LEN(A1)) makes the list 1, 2, 3… up to the length of the text.
  2. MID(A1, …, 1) pulls out each character on its own.
  3. &UNICHAR(822) adds the strike mark after each one.
  4. CONCAT joins them back into one string.

Fill it down the column like any formula. TEXTJOIN("",,MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822)) does the same and lets you skip empty cells.

To leave spaces unstruck, so each word gets its own line:

=CONCAT(LET(c,MID(A1,SEQUENCE(LEN(A1)),1),IF(c=" ",c,c&UNICHAR(822))))

Excel 2019 and earlier

Excel 2019 has CONCAT but not SEQUENCE. Build the list of positions with ROW(INDIRECT()) instead and confirm the formula with Ctrl + Shift + Enter so it runs as an array formula:

=CONCAT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)&UNICHAR(822))

Excel 2016 without a Microsoft 365 subscription has neither CONCAT nor TEXTJOIN. There, the practical options are real formatting (Ctrl + 5) or pasting struck text from the tool on this page.

Google Sheets

Sheets supports regular expressions, which makes it a one-liner. (.) matches each character and $1 puts it back with the mark after it:

=REGEXREPLACE(A1,"(.)","$1"&UNICHAR(822))

The array version also works, and mirrors the Excel formula:

=JOIN("",ARRAYFORMULA(MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822)))

To skip spaces in Sheets, match only non-spaces: =REGEXREPLACE(A1,"(\S)","$1"&UNICHAR(822)).

Codes that look right but aren’t

Pages that copy each other repeat a few wrong versions:

Formula What you get Why
UNICHAR(822) a̶ struck text Correct: 822 is U+0336 in decimal
CHAR(822) #VALUE! CHAR only goes up to 255
UNICHAR(336) Ő 336 in decimal is U+0150, a Hungarian letter
UNICHAR(0336) Ő Leading zeros don’t make it hex
UNICHAR(818) a̲ underlined 818 is U+0332, the combining low line

If you want a different line, the neighbouring marks work the same way: UNICHAR(821) is the short stroke (U+0335), UNICHAR(823) a short slash and UNICHAR(824) a long slash (U+0338).

Remove the marks again

SUBSTITUTE takes the mark back out, leaving plain text you can sort, search and match:

=SUBSTITUTE(A1,UNICHAR(822),"")

For text that mixes several marks, the remove strikethrough tool strips them all at once.

Strike rows automatically with conditional formatting

For a task list where finished rows should be crossed out, you want formatting rather than new text, and conditional formatting does it without any formula output:

Excel

  1. Select the rows, for example A2:D100.
  2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
  3. Enter =$C2="Done", adjusting the column and the word.
  4. Click Format, tick Strikethrough on the Font tab, then OK.

Google Sheets

  1. Select the range and open Format → Conditional formatting.
  2. Under Format cells if, choose Custom formula is and enter =$C2="Done".
  3. Click the strikethrough button in the formatting style, then Done.

The $ before the column letter keeps every cell in the row looking at column C. A tick box works too: use =$C2=TRUE with a checkbox column.

Check whether a cell is struck through

No worksheet function reports formatting, but each app has a workaround.

Excel: the old Excel 4 macro function GET.CELL returns TRUE when a cell’s font has strikethrough (information type 23). It only works inside a defined name:

  1. Click cell B2, open Formulas → Name Manager → New.
  2. Name it IsStruck and set Refers to to =GET.CELL(23,Sheet1!A2).
  3. In B2, type =IsStruck and fill down.
  4. Save the workbook as .xlsm, because GET.CELL counts as a macro.

Google Sheets: add a custom function in Extensions → Apps Script:

function ISSTRUCK(a1) {
  return SpreadsheetApp.getActiveSheet().getRange(a1).getFontLine() === 'line-through';
}

Then use =ISSTRUCK("A2"), with the address in quotes. Neither method recalculates the instant you change formatting; edit a cell or reopen the file to refresh.

Things to know

  • Length doubles. Every character gains a mark, so LEN on the result is about twice the original. Keep that in mind for character limits.
  • Search and lookups miss it. VLOOKUP, MATCH and Find compare exact characters. Look up the original column, not the struck one.
  • Fonts matter. Calibri, Arial and the Sheets defaults draw the line cleanly. Some decorative fonts place it a little high or leave gaps.
  • It travels. Unlike formatting, struck text from a formula keeps its line when pasted into Slack, WhatsApp, Instagram or an email subject.

Copy-paste version

For the places where Excel formula formatting won't follow your text, like file names, chat apps or a social bio.

4 characters, 1 words
Ctrl + Enter copies the first result

Runs in your browser. Nothing you type is uploaded.

  • strikethroughD̶o̶n̶e̶
  • short strikeD̵o̵n̵e̵
  • wavy strikeD̴o̴n̴e̴
  • slashD̷o̷n̷e̷
  • long slashD̸o̸n̸e̸
  • double slashD̸̷o̸̷n̸̷e̸̷
  • strike + underlineD̶̲o̶̲n̶̲e̶̲
  • strike + overlineD̶̅o̶̅n̶̅e̶̅
  • underlineD̲o̲n̲e̲
  • double underlineD̳o̳n̳e̳
  • overlineD̅o̅n̅e̅
  • dottedD̤o̤n̤e̤
  • waveD̰o̰n̰ḛ
  • x belowD͓o͓n͓e͓
  • redacted████

Questions people ask

Is there a formula for strikethrough in Excel?

Not one that applies formatting: formulas return values, not styles. But =CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(822)) in Excel 365 returns the text of A1 with a combining strikethrough mark after every character, which looks crossed out and survives copy and paste.

What is UNICHAR(822)?

822 is the decimal code of U+0336, the combining long stroke overlay. It draws a line through the character before it. UNICHAR turns the code into the character; it has been in Excel since 2013 and is in Google Sheets.

Why does CHAR(822) give an error?

CHAR only covers codes 1 to 255 from the old Windows character set, so 822 returns #VALUE!. Use UNICHAR, which takes any Unicode code point.

How do I strike through a row when a task is done?

Use conditional formatting. In Excel: select the rows, Home → Conditional Formatting → New Rule → Use a formula, enter =$C2="Done", then Format → Font → Strikethrough. In Google Sheets: Format → Conditional formatting → Custom formula is, same formula, then the strikethrough button.

Can a formula tell whether a cell has strikethrough?

Not a normal one. In Excel, define a name that uses the old GET.CELL(23, cell) macro function, which returns TRUE for struck cells, and save as .xlsm. In Google Sheets, a short Apps Script custom function can read getFontLine() and return whether it is line-through.

Keep going