Home

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
luism95
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
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)

@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

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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies