+ Reply to Thread
Results 1 to 30 of 30

Seperate contents of a column of numbers based on their length

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Seperate contents of a column of numbers based on their length

    Hi Guys

    I have a spreadsheet with numbers like this

    9404388
    9404374
    9404391
    9405695
    38301663
    9409724
    1791016
    9435145
    9530758
    9440464
    9441182
    71000569
    9467438


    There are a thousand numbers like this, I was wondering how I can move the cells with 7 numbers to the right column, and keep the cells with 8 numbers where they are (or move them to the 3rd column to the right)

    Thanks
    -Tony

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    Does this helps you?

    =IF(LEN(A1)=7,A1,"")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by Fotis1991 View Post
    Does this helps you?

    =IF(LEN(A1)=7,A1,"")

    Hello

    Thanks for responding quickly, I have tried using that formula but dont seem to be producing the required results. Am I doing it correctly? I am am highlighting all 1200 numbers and inserting the code into the function area at the top. I have also tried highlighting all the numbers and clicking insert function. I am using office 2010.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    Assuming that your data start in A1, then put the suggested formula in B1 and copy down.

  5. #5
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by Fotis1991 View Post
    Assuming that your data start in A1, then put the suggested formula in B1 and copy down.
    Ahh I see. I notice that it is only doing the first number however. How do I get it to do every number in the A column, (without having to input =IF(LEN(A1)=7,A1,"") =IF(LEN(A2)=7,A2,"")=IF(LEN(A3)=7,A3,"") etc etc in each cell in the B column?

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    Hover the mouse pointer over the small black box in the bottom right corner of the cell you want to copy down. The mouse pointer will change from a white cross to a thinner black cross. Doubleclick on the small black box , and the formula/cell contents will be copied down as far as there is contiguous data in the column

    OR

    click the first cell that has the formula once then scroll to the last cell hold shift key and click that cell once, then in your menu bar at the top choose>edit>fill>down and hey presto cells filled!

  7. #7
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by Fotis1991 View Post
    Hover the mouse pointer over the small black box in the bottom right corner of the cell you want to copy down. The mouse pointer will change from a white cross to a thinner black cross. Doubleclick on the small black box , and the formula/cell contents will be copied down as far as there is contiguous data in the column

    OR

    click the first cell that has the formula once then scroll to the last cell hold shift key and click that cell once, then in your menu bar at the top choose>edit>fill>down and hey presto cells filled!

    Hi

    When I do what your saying it does this,

    Untitled.png



    Hope you can see that it is only copying the contents of A1 only its not going to a2 a3 a4 etc?

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    Upload the workbook,pls. Not a picture.

  9. #9
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Sorry I have added the workbook
    Attached Files Attached Files

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    See the example.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Thank you very much
    I dont suppose you know how to remove the spaces between the cells in columns b and c?

  12. #12
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    In E1 and copy down, this ARRAY formula..

    =INDEX(B$1:B$1300;SMALL(IF(B$1:B$1300<>"";ROW(B$1:B$1300);1000);ROW()-ROW(E$1)+1)-ROW(E$1)+1)
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Thanks very much for your help! you are awesome!

  14. #14
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    ..............................

  15. #15
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Hi Fotis

    I have noticed some payroll numbers missing from the last spreadsheet you attached. Could you do the same thing but with this spreadsheet I am attaching which is the original spreadsheet. ?
    Attached Files Attached Files

  16. #16
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    You JUST need to drag-copy formulas down in my previous example. Did you learn how to drag-down the formulas from 1 cell?

    Take a look to this too.

    http://blog.contextures.com/archives...-formula-down/

  17. #17
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by Fotis1991 View Post
    You JUST need to drag-copy formulas down in my previous example. Did you learn how to drag-down the formulas from 1 cell?

    Take a look to this too.

    http://blog.contextures.com/archives...-formula-down/
    Ok will try it.

  18. #18
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Ok I am doing that and it is still not working. Using your latest spreadsheet, I am highlighting F1 all the way down to F52 and then clicking the small bottom right dot on cell F52 twice, all it does is repeat what is in F1 to F52 again. If I drag the dot downwards from F52 it copies just 71021146 . Sorry I am really no good at excel

  19. #19
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Seperate contents of a column of numbers based on their length

    select only F1
    move the cursor to the bottom right corner of F1 until it becomes a black + sign
    double-click
    :-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  20. #20
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by JosephP View Post
    select only F1
    move the cursor to the bottom right corner of F1 until it becomes a black + sign
    double-click
    :-)

    Hi

    All that seems to do is copy the contents of F1 downwards. See attached pic.
    Untitled.jpg

  21. #21
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Seperate contents of a column of numbers based on their length

    you have manual calculation mode on-press f9

  22. #22
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by JosephP View Post
    you have manual calculation mode on-press f9
    Pressed f9, still copies the first entry only....

  23. #23
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Seperate contents of a column of numbers based on their length

    press f9 after filling the formulas down (or turn automatic calculation on)

  24. #24
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by JosephP View Post
    press f9 after filling the formulas down (or turn automatic calculation on)
    Ok I have done that, I am now getting a series of Zeros after cell f52.

    What I really want is two columns made from this spreadsheet attached, column A needs to show numbers with 7 digits, column b needs to show numbers with 8 digits. What Fotis is done is correct but is missing some numbers. I am not good with computers let alone excel.
    Attached Files Attached Files

  25. #25
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    In my latest example, i dragged the formula in column C until cell C233. That's why you don't get ALL your numbers in column E.Drag down as you want and then drag down formula in column E until to see the first zero...

  26. #26
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Is what I have done correct? see attached
    Attached Files Attached Files

  27. #27
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    See the example Tony. Columns J & l ARE JUST FOR TESTING. yOY CAN DELETE THESE...
    Attached Files Attached Files

  28. #28
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Seperate contents of a column of numbers based on their length

    Nevermind - deleted
    Last edited by protonLeah; 03-08-2013 at 05:41 PM.
    Ben Van Johnson

  29. #29
    Registered User
    Join Date
    03-05-2013
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Seperate contents of a column of numbers based on their length

    Quote Originally Posted by Fotis1991 View Post
    See the example Tony. Columns J & l ARE JUST FOR TESTING. yOY CAN DELETE THESE...
    Thank you very much fotis, I think that about covers what I needed

  30. #30
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Seperate contents of a column of numbers based on their length

    ...................

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1