- 544K Members
- 5,333 Online
- 649K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- copy and paste digit into multiple cells

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2019 12:39 PM

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?

Labels:

3 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2019 01:16 PM

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)

=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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2019 01:32 PM - edited 07-26-2019 02:02 PM

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:

- The Joining operator =C1&B1&D1&E1&F1 ...etc
- The Concatenate Function
- The CONCAT function
- 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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2019 01:52 PM

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

Related Conversations

Questions About Pasting in OneNote Online

Asia_Nidyra_Thomas
in
OneNote
on
12-10-2019
116
Views

0 Likes

0 Replies

Mutliple Time Zones in Calendar - copy outlook?

Lordonightmares
in
Teams Developer
on
12-10-2019
75
Views

0 Likes

1 Replies

Not able to copy and paste two of six pie charts from Excel worksheet

kkb_IHC
in
Excel
on
10-22-2019
569
Views

0 Likes

19 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
159
Views

0 Likes

0 Replies

Copiare foglio con grafico su una nuova cartella

MCristinaMaestri
in
Excel
on
07-29-2019
44
Views

0 Likes

0 Replies

How to copy or duplicate an existing forms quiz

Olaf Nennker
in
Microsoft Forms
on
10-31-2018
2,426
Views

0 Likes

2 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft