Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Pull Each Each Digit From Number Into Single Cells

1. Senior Member
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

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## 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. Member
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

Excel Video Tutorials / Excel Dashboards Reports

4. Established Member
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. Member
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. ## 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)

7. I agreed to these rules
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. .
Join Date
1st September 2010
Posts
10,912

## Re: Pull Each Each Digit From Number Into Single Cells

Welcome to the forum, sreenivasr.

Thank you.

Excel Video Tutorials / Excel Dashboards Reports

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

#### 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