Forum Discussion

Copper Contributor

Sep 30, 2020

Solved

Displaying individual digits from a serial number

I have to create a spreadsheet to create serial numbers then break the serial number down to it's individual digits. I might not be explaining this that well so an example of what i need to achieve i...

excel

Formulas and Functions

HansVogelaar
Sep 30, 2020
Pete_Atkinson

In B2:

=MID($A2,COLUMN()-1,1)

Fill to the right as far as you want, then fill down.

HansVogelaar

MVP

Sep 30, 2020

Pete_Atkinson

In B2:

=MID($A2,COLUMN()-1,1)

Fill to the right as far as you want, then fill down.

Pete_Atkinson
Copper Contributor
Sep 30, 2020
HansVogelaar Thank you so much for that.

If it isn't too much trouble would you mind explaining the formula so i know how it's working?
- HansVogelaar
  MVP
  Sep 30, 2020
  Pete_Atkinson
  
  The COLUMN() function returns the column number of the cell containing the formula: 1 in column A, 2 in column B, etc.
  
  The MID function has syntax MID(textstring, start, length). It returns length characters beginning at position start in textstring.
  
  So for example in B2, MID($A2, COLUMN()-1, 1) evaluates to MID($A2, 2-1, 1) which is equivalent to MID($A2, 1, 1). This returns 1 character starting at position 1, i.e. the first digit of the number in A2.
  
  And in C2, MID($A2, COLUMN()-1, 1) evaluates to MID($A2, 3-1, 1) which is equivalent to MID($A2, 2, 1). This returns 1 character starting at position 2, i.e. the second digit of A2.