SOLVED

Displaying individual digits from a serial number

%3CLINGO-SUB%20id%3D%22lingo-sub-1728662%22%20slang%3D%22en-US%22%3EDisplaying%20individual%20digits%20from%20a%20serial%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728662%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20create%20a%20spreadsheet%20to%20create%20serial%20numbers%20then%20break%20the%20serial%20number%20down%20to%20it's%20individual%20digits.%20I%20might%20not%20be%20explaining%20this%20that%20well%20so%20an%20example%20of%20what%20i%20need%20to%20achieve%20is%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20A2-1234567890%20then%20Cell%20B2%20will%20display%201%2C%20C2%20will%20display%202%2C%20D2%20will%20display%203%20and%20so%20on%20for%20the%20whole%20of%20the%2010%20digit%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20and%20all%20advice%20or%20suggestions%20will%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPete%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1728662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728800%22%20slang%3D%22en-US%22%3ERe%3A%20Displaying%20individual%20digits%20from%20a%20serial%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F125358%22%20target%3D%22_blank%22%3E%40pete%20atkinson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMID(%24A2%2CCOLUMN()-1%2C1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20as%20far%20as%20you%20want%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728905%22%20slang%3D%22en-US%22%3ERe%3A%20Displaying%20individual%20digits%20from%20a%20serial%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20for%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20isn't%20too%20much%20trouble%20would%20you%20mind%20explaining%20the%20formula%20so%20i%20know%20how%20it's%20working%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1729380%22%20slang%3D%22en-US%22%3ERe%3A%20Displaying%20individual%20digits%20from%20a%20serial%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F125358%22%20target%3D%22_blank%22%3E%40pete%20atkinson%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20if%20you%20are%20a%20MS365%20subscriber%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMID(A2%2CSEQUENCE(1%2CLEN(A2))%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted
Best Response confirmed by pete atkinson (Occasional Contributor)
Solution

@pete atkinson 

In B2:

 

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

 

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

Highlighted

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

Highlighted

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

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

 

Highlighted

@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.

Highlighted
Thank you for taking the time to explain it for me