Sep 30 2020 04:48 AM
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
Sep 30 2020 05:01 AM
SolutionSep 30 2020 05:34 AM - edited Sep 30 2020 05:43 AM
@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?
Sep 30 2020 07:42 AM
Sep 30 2020 07:45 AM
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.
Sep 30 2020 08:12 AM
Sep 30 2020 05:01 AM
SolutionIn B2:
=MID($A2,COLUMN()-1,1)
Fill to the right as far as you want, then fill down.