SOLVED

Displaying individual digits from a serial number

Occasional Contributor

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 is this

Cell A2-1234567890 then Cell B2 will display 1, C2 will display 2, D2 will display 3 and so on for the whole of the 10 digit number.

Any and all advice or suggestions will be greatly appreciated.

Pete

5 Replies
best response confirmed by pete atkinson (Occasional Contributor)
Solution

Re: Displaying individual digits from a serial number

In B2:

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

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

Re: Displaying individual digits from a serial number

@Hans Vogelaar  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?

Re: Displaying individual digits from a serial number

@pete atkinson As a variant, if you are a MS365 subscriber:

``=MID(A2,SEQUENCE(1,LEN(A2)),1)``

Re: Displaying individual digits from a serial number

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.

Re: Displaying individual digits from a serial number

Thank you for taking the time to explain it for me