copy and paste digit into multiple cells

[luism95]

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? 

[#2]

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)

[#3]

@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

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

Hope That Helps

Nabil Mourad

[#4]

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