Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Pull Each Each Digit From Number Into Single Cells

  1. #1
    Join Date
    19th March 2003
    Posts
    225

    Pull Each Each Digit From Number Into Single Cells

    Hi excel expert

    Attached is the sample file.

    Basically I have a number in column A, just wondering is that any faster to allocate the number into individual cell as shown in the attached file.

    Regards
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Link Number Into Individual Cell

    Hi,

    May be..

    In C3 and copied down & across,

    =IF(COLUMNS($C3:C3)>LEN(REPT("0",9-LEN(SUBSTITUTE(TEXT($A3,"#.00"),".","")))),MID(REPT("0",9-LEN(SUBSTITUTE(TEXT($A3,"#.00"),".","")))&SUBSTITUTE(TEXT($A3,"#.00"),".",""),COLUMNS($C3:C3),1),"")

    Note: Formula edited 1 time

  3. #3
    Join Date
    5th May 2008
    Location
    London, UK
    Posts
    35

    Re: Pull Each Each Digit From Number Into Single Cells

    Here's another alternative.

    Set up an named array constant called Arr which is defined as:
    ={1,2,3,4,5,6,7,8,9}

    Then select cells C3:K3 and complete this array range formula with CTRL+SHIFT+ENTER:
    =MID(TEXT($A3,"0.00"),(SUBSTITUTE(SMALL((ISNUMBER(-(MID(TEXT($A3,"0.00"),Arr,1)))*Arr),Arr),0,LEN(TEXT($A3,"0.00"))+1)),1)

    Then fill the formula down the table.

    Example attached.

    Colin
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th January 2006
    Posts
    524

    Re: Pull Each Each Digit From Number Into Single Cells

    Very nice and interesting formulas Kris n Colin.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    5th May 2008
    Location
    London, UK
    Posts
    35

    Re: Pull Each Each Digit From Number Into Single Cells

    If anyone wants a breakdown on how the formula works then I would be happy to oblige...

    Colin

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Pull Each Each Digit From Number Into Single Cells

    Here's another option:

    =IF(LEN(100*$A3)<12-COLUMN(),"",MID(TEXT(100*$A3,"000000000"),COLUMN()-2,1)+0)
    Entia non sunt multiplicanda sine necessitate.

  7. #7
    Join Date
    24th October 2012
    Posts
    3

    Re: Pull Each Each Digit From Number Into Single Cells

    Could you please explain what will be the Formula for Extracting 15 Digit ? Thanks ! - Sreenivas

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    1st September 2010
    Posts
    10,912

    Re: Pull Each Each Digit From Number Into Single Cells

    Welcome to the forum, sreenivasr.

    Please do not post questions in threads started by other members. Start your own thread, give it a clear and concise title and explain your problem fully.

    If this thread can help explain your issue, you can include a link by copying the URL from the address bar of your browser and pasting into your message.

    Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: June 11th, 2008, 08:56
  2. Replies: 4
    Last Post: June 10th, 2008, 09:06
  3. Seperate 2 Digit Number Into 2 Single Digit Numbers
    By Triggman in forum Excel General
    Replies: 4
    Last Post: April 3rd, 2008, 11:59
  4. is a number or a digit
    By Richnl in forum Excel General
    Replies: 3
    Last Post: June 22nd, 2006, 17:27
  5. Replies: 5
    Last Post: November 26th, 2005, 02:07

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
  •  
porno