copy and paste digit into multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-775645%22%20slang%3D%22en-US%22%3Ecopy%20and%20paste%20digit%20into%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775645%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20any%20way%20to%20copy%20a%209%20digit%20number%20that%20is%20in%20one%20cell%20and%20paste%20it%20into%209%20separate%20cells%2C%20one%20digit%20in%20each%20cell%2C%20and%20vice%20versa%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-775645%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-775785%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20digit%20into%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775785%22%20slang%3D%22en-US%22%3EIn%20order%20to%20separate%20a%209%20digit%20number%20into%20one%20digit%20per%20cell%2C%20assuming%20the%20number%20is%20located%20in%20cell%20A1%2C%20you%20may%20use%20this%20formula%20(copy%20over%209%20columns)%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DMID(%24A%241%2CCOLUMN(A%3AA)%2C1)%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20order%20to%20join%209%20separate%20numbers%2C%20assuming%20the%20numbers%20are%20located%20in%20the%20range%20A1%3AI1%2C%20you%20may%20use%20this%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCONCATENATE(A1%2CB1%2CC1%2CD1%2CE1%2CF1%2CG1%2CH1%2CI1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775797%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20digit%20into%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382850%22%20target%3D%22_blank%22%3E%40luism95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20you%20type%209%20digits%20in%20cell%20A1%20(Note%20that%20digits%20are%20right%20aligned)%20and%20then%20say%20in%20cell%20C1%20you%20need%20the%20first%20digit%20only%2C%20then%3C%2FP%3E%3CP%3Ein%20C1%20type%3A%3C%2FP%3E%3CP%3E%3DRIGHT(LEFT(%24A1%2CCOLUMNS(%24A%241%3AA1))%2C1)*1%3C%2FP%3E%3CP%3ECopy%20this%20function%20to%20the%20right%208%20more%20columns%20and%20you%20get%20the%209%20digits%20parsed%20%2C%20each%20in%20a%20separate%20cell%20and%20they%20are%20still%20recognized%20as%20numbers%20(Right%20aligned)%3C%2FP%3E%3CP%3ENote%20that%26nbsp%3B%20using%20only%20text%20functions%20such%20as%20Right%2C%20Mid%2C%20Left%2CReplace%2C%20Substitute...%20%26gt%3B%26gt%3B%20returns%20a%20TEXT%20which%20is%20Left%20Aligned%20and%20cannot%20be%20reused%20in%20calculations...%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775832%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20digit%20into%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775832%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20the%20collection%3A%3C%2FP%3E%0A%3CP%3EIf%20numbers%20are%20in%20the%20column%2C%20they%20could%20be%20split%20by%20Data-%26gt%3BText%20to%20Columns%20with%20fixed%20width%20and%20destination%20into%20another%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20combine%20back%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%3DSUMPRODUCT(B1%3AJ1*%7B100000000%2C10000000%2C1000000%2C100000%2C10000%2C1000%2C1000%2C10%2C1%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%20Like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20823px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124665i54338A1392CDCC10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775803%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20digit%20into%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382850%22%20target%3D%22_blank%22%3E%40luism95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20type%209%20digits%20in%20cell%20A1%20(Note%20that%20digits%20are%20right%20aligned)%20and%20then%20say%20in%20cell%20C1%20you%20need%20the%20first%20digit%20only%2C%20then%3C%2FP%3E%3CP%3Ein%20C1%20type%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DRIGHT(LEFT(%24A1%2CCOLUMNS(%24A%241%3AA1))%2C1)*1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECopy%20this%20function%20to%20the%20right%208%20more%20columns%20and%20you%20get%20the%209%20digits%20parsed%20%2C%20each%20in%20a%20separate%20cell%20and%20they%20are%20still%20recognized%20as%20numbers%20(Right%20aligned)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20screenshot%20of%20what%20I%20did%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DRIGHT(LEFT(%24A1%2CCOLUMNS(%24A%241%3AA1))%2C1)*1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124663iA4DD51D6726D2B83%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Parse%209%20Digits.png%22%20title%3D%22Parse%209%20Digits.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENote%20that%26nbsp%3B%20using%20only%20text%20functions%20such%20as%20Right%2C%20Mid%2C%20Left%2CReplace%2C%20Substitute...%20%26gt%3B%26gt%3B%20returns%20a%20%3CSTRONG%3ETEXT%3C%2FSTRONG%3Ewhich%20is%20%3CSTRONG%3ELeft%20Aligned%3C%2FSTRONG%3Eand%20cannot%20be%20reused%20in%20calculations...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%20Note%20that%20although%20you%20could%20split%20the%20number%20into%20multiple%20columns%20by%20using%20%22%3CSTRONG%3EText%20To%20Column%3C%2FSTRONG%3E%22%20%26gt%3B%26gt%3B%3CSTRONG%3Eit's%20not%20dynamic%3C%2FSTRONG%3Eand%20if%20the%20source%20column%20expands%20%26gt%3B%26gt%3B%20You%20will%20have%20to%20repeat%20the%20process.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20%3CSTRONG%3Eregroup%3C%2FSTRONG%3Ethem%20you%20can%20use%20one%20of%20the%20following%3A%3C%2FP%3E%3COL%3E%3CLI%3EThe%20Joining%20operator%20%3DC1%26amp%3BB1%26amp%3BD1%26amp%3BE1%26amp%3BF1%20...etc%3C%2FLI%3E%3CLI%3EThe%20Concatenate%20Function%3C%2FLI%3E%3CLI%3EThe%20CONCAT%20function%3C%2FLI%3E%3CLI%3EFlash%20Fill%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENote%20that%20All%20Re-grouping%20options%20will%20return%20%3CSTRONG%3ETEXT%20(Left%20Aligned)%3C%2FSTRONG%3Eso%20to%20Change%20it%20back%20to%20a%20%3CSTRONG%3ENumber%20(Right%20aligned)%20%26gt%3B%26gt%3B%20Multiply%20by%20one%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E%3D(C1%26amp%3BD1%26amp%3BE1%26amp%3BF1%26amp%3BG1%26amp%3BH1%26amp%3BI1%26amp%3BJ1%26amp%3BK1)*1%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124664i4538B9924C4C9232%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Back%20To%20Grouped.png%22%20title%3D%22Back%20To%20Grouped.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHope%20That%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Is there any way to copy a 9 digit number that is in one cell and paste it into 9 separate cells, one digit in each cell, and vice versa? 

3 Replies
Highlighted
In order to separate a 9 digit number into one digit per cell, assuming the number is located in cell A1, you may use this formula (copy over 9 columns):

=MID($A$1,COLUMN(A:A),1)

In order to join 9 separate numbers, assuming the numbers are located in the range A1:I1, you may use this formula:

=CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1)
Highlighted

@luism95 

Hi 

If you type 9 digits in cell A1 (Note that digits are right aligned) and then say in cell C1 you need the first digit only, then

in C1 type:

=RIGHT(LEFT($A1,COLUMNS($A$1:A1)),1)*1

Copy this function to the right 8 more columns and you get the 9 digits parsed , each in a separate cell and they are still recognized as numbers (Right aligned)

 

Here is a screenshot of what I did

=RIGHT(LEFT($A1,COLUMNS($A$1:A1)),1)*1

 

Parse 9 Digits.png

 

Note that  using only text functions such as Right, Mid, Left,Replace, Substitute... >> returns a TEXT which is Left Aligned and cannot be reused in calculations...

Also Note that although you could split the number into multiple columns by using "Text To Column" >> it's not dynamic and if the source column expands >> You will have to repeat the process.

 

to regroup them you can use one of the following:

  1. The Joining operator =C1&B1&D1&E1&F1 ...etc
  2. The Concatenate Function
  3. The CONCAT function
  4. Flash Fill

Note that All Re-grouping options will return TEXT (Left Aligned) so to Change it back to a Number (Right aligned) >> Multiply by one:
=(C1&D1&E1&F1&G1&H1&I1&J1&K1)*1

Back To Grouped.png

Hope That Helps

Nabil Mourad

Highlighted

For the collection:

If numbers are in the column, they could be split by Data->Text to Columns with fixed width and destination into another columns.

 

To combine back as

=SUMPRODUCT(B1:J1*{100000000,10000000,1000000,100000,10000,1000,1000,10,1})

  Like this

image.png