• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I get several digits in 1 cell to its own cell

I have various groups of numbers ranging from 3 numbers all the way up to 12 numbers all in 1 cell. What formula can I use or Macro to separate them and put them in there own cells?

You will see them all in column A. I want to place them in BCDEFGHIJKLM...


Thanks
 

Attachments

  • put numbers in there own cell.jpg
    put numbers in there own cell.jpg
    126.7 KB · Views: 8
  • Numbers in own cells.xlsx
    8.3 KB · Views: 7
I am not sure if its the formula or excel. I have several numbers that start with zero and excel is dropping it so a 4 digit number becomes a 3 digit number

Any clues?
 
Here is a pic and a file. I need t have the "0" show and also take it out of B and place it in a cell by itself

Thanks
 

Attachments

  • zero.xlsx
    8 KB · Views: 2
  • zeros.xlsx.png
    zeros.xlsx.png
    50.1 KB · Views: 2
Hi Larry ,

See this now.

Depending on whether the number has 4 digits or 5 , you need to use the UDF accordingly.

Narayan
 

Attachments

  • Copy of Quinto.xlsm
    47.6 KB · Views: 3
Hi Larry ,

See this now.

Depending on whether the number has 4 digits or 5 , you need to use the UDF accordingly.

Narayan

Narayan,

A little confused. The spreadsheet is missing the first digit in all rows and what is the Macro doing? I can not seem to assign it
 

Attachments

  • copy of Narsheet.jpg
    copy of Narsheet.jpg
    212.6 KB · Views: 2
Hi Larry ,

I do not know what you wish to do.

I can explain what the macro does.

The macro is to be called as follows :

=MYSPLIT(range which contains the number , position of digit which is to be returned , position from where the counting is to start)

Thus , let us assume there is a 5 digit number , but you want only the right-most 4 digits to be returned in their separate cells. Thus , in the 4 cells , you would have the following 4 formulae :

=MYSPLIT($A3,1,2)

=MYSPLIT($A3,2,2)

=MYSPLIT($A3,3,2)

=MYSPLIT($A3,4,2)

Because we do not want to enter a different formula in each cell , we use the COLUMN function , which will change appropriately as we copy the formula across :

=MYSPLIT($A3,COLUMN(A3),2)

However , when you have only a 4-digit number , which is to be separated , then in the 4 cells , you need to have the formulae :

=MYSPLIT($A3,1,1)

=MYSPLIT($A3,2,1)

=MYSPLIT($A3,3,1)

=MYSPLIT($A3,4,1)

To avoid entering a different formula in each cell , we now need to use :

=MYSPLIT($A3,COLUMN(A3),1)

Narayan
 
Hi Larry ,

I do not know what you wish to do.

I can explain what the macro does.

The macro is to be called as follows :

=MYSPLIT(range which contains the number , position of digit which is to be returned , position from where the counting is to start)

Thus , let us assume there is a 5 digit number , but you want only the right-most 4 digits to be returned in their separate cells. Thus , in the 4 cells , you would have the following 4 formulae :

=MYSPLIT($A3,1,2)

=MYSPLIT($A3,2,2)

=MYSPLIT($A3,3,2)

=MYSPLIT($A3,4,2)

Because we do not want to enter a different formula in each cell , we use the COLUMN function , which will change appropriately as we copy the formula across :

=MYSPLIT($A3,COLUMN(A3),2)

However , when you have only a 4-digit number , which is to be separated , then in the 4 cells , you need to have the formulae :

=MYSPLIT($A3,1,1)

=MYSPLIT($A3,2,1)

=MYSPLIT($A3,3,1)

=MYSPLIT($A3,4,1)

To avoid entering a different formula in each cell , we now need to use :

=MYSPLIT($A3,COLUMN(A3),1)

Narayan

Narayan,

What I'm wanting to do is to take the numbers that are in 1 cell, separate them and put each digit in its own cell. Example: if A:1 has 123456789....12.

I want to change that so B:1 had 1, C:1 has 2, D:1 has 3, E:1 has 4 and so on ..... Up to a 12 digit number M:1 has 12
 
Hi Larry ,

Surely you can manage this with the explanation that has been given.

If you use the formula :

=MYSPLIT($A1,COLUMN(A1),1)

in any unused cell , and put a 12-digit number in A1 , then the above formula will return the left-most one digit in the cell where this formula has been entered.

If you now copy this formula across , the other cells will display the remaining digits. If you copy this formula into 12 cells , you will get all the 12 digits in them.

Narayan
 
Back
Top