23 Aug 2019

FEN notation provides the necessary info to restart a chess game from a given position. See how I created a FEN viewer in Excel!

 

Excel Files

1)Kevin’s FEN viewer basic,  2)Kevins-FEN-viewer-plus-v3 (database, FEN explanation).

 

How Does FEN Work?

Given this FEN text…

r6k/2R5/6R1/pp1Ppp2/8/Pn2B1Pr/4KP2/8 w – – 0 1

…we can create this chess position:

 

 

 

 

 

 

 

 

A FEN is split into 8 parts separated by “/”. Each part is a row on a chess board.

r6kdescribes row 8 at the top. Let’s examine each item:

  • lowercase “r” = black rook (top left a8),
  • number “6” = 6 consecutive blanks squares
  • lowercase “k” = black King (top right h8)

2R5” describes row 7.

  • number “2” = blank squares (a7 & b7)
  • uppercase “R” = white rook
  • number “5” = 5 blank spaces

Near the end the “w” indicates that it’s white’s move.

 

Here we see each row’s FEN code:

 

 

 

 

 

 

 

 

 

 

How Does FEN Viewer work?

Robert Gascon’s ‘Excel Chess Games Viewer‘ inspired me to create this FEN viewer two weeks ago. I used 6 steps spread across 42 columns.

Unhide columns to see the formulas:

  • on the ribbon select View and check Headings
  • select columns N to BI
  • right click & unhide

 

Highlevel Explanation

Steps start in column P and move to the right:

Step 1 splits FEN r6k/2R5/6R1/pp1Ppp2/8/Pn2B1Pr/4KP2/8 out per row in column P

Note: each number = consecutive blank squares. Step 6 has 8 cells representing each square in a chess row. A FEN is compact. My idea? Spread the FEN over 8 squares of a chess row.

r6k” becomes “r666666k”. “2R5″ becomes “22R55555”  (step 6 in column AX). Audit formulas in all steps to understand fully.

 

Final Formula

On the chess board look at cell C7 array formula:

=IFERROR(INDEX($BH$7:$BH$19,MATCH(TRUE,EXACT(AX7,$BG$7:$BG$19),0)),””)

Cell C7 looks for AX7 valuer” in column BG. The answer is a chess icon from column BH.

A couple of important parts of the formula:

  • “r” is different from “R” so I used MATCH(TRUE,EXACT(
  • I used IFERROR (Numbers are blank squares. There’s nothing to display.)

 

More About FEN

A good summary from Wikipedia:

FEN is based on a system developed by Scottish newspaper journalist David Forsyth. Forsyth’s system became popular in the 19th century; Steven J. Edwards extended it to support use by computers. FEN is an integral part of the Portable Game Notation for chess games, since FEN is used to define initial positions other than the standard one. FEN does not provide sufficient information to decide whether a draw by threefold repetition may be legally claimed or a draw offer may be accepted; for that, a different format such as Extended Position Description is needed.

 

More Chess in Excel!

 

Learn Chess!

 

Collect FENs!

FEN examples from  ‘Kevin’s FEN viewer plus v3’:

  • Row 16 agadmator: find the next move (Carlsen vs Anand)

 

Agadmator sometimes asks us to pause the video and find the best next move.

Subscribe to his YouTube channel! You will learn a lot.

 

 

  • Row 17 my game: find the mate combo! (black’s move)

Use my Excel file to collect your FENs.

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.

I know….two chess related Excel posts in a week is a bit too much for most. But some of us love both so why not? 🙂

 

 

2 Comments

  1. Robert H. Gascon says:

    Hello Kevin,
    I love your ingenious creation! This is my first time to know of FEN.
    Cheers,
    Robert

    1. Kevin Lehrbass says:

      Hi Robert,
      Well…your Chessviewer inspired me to attempt this so thanks to you!
      Kevin

Post a comment