Spilting 1 cells contents into 5 columns over an entry of 1 to five characters

[Michael]

Can you guide me please

I have one cell that will hve anything up to a 5 digit number in this, I
what to place the respective digit in to separate columns

ie.
If I enter ABCDE in the receiving cell, I use the MID(Cell,3,1) which will
return me the C

A | B | C | D | E
| F |
1 |ABCDE| A | B | C | D |
E |

Which is acheived with
Cell B1 =MID($A$1,1,1)
Cell C1 =MID($A$1,2,1)
Cell D1 =MID($A$1,3,1)
Cell E1 =MID($A$1,4,1)
Cell F1 =MID($A$1,5,1)

Gets me the result I want when the figure is five digits long

However when I only have a four digit result say BCDE

the result is as follows

A | B | C | D | E
| F |
1 |BCDE | B | C | D | E |
|

I need to display it

A | B | C | D | E
| F |
1 |BCDE | | B | C | D |
E |

Hope this is clear

TIH

Michael

 

[Max]

If it's 5 digit figures (numbers) only in A1 down, think we could try in B1:

=IF(MID(TEXT($A1,"00000"),COLUMN(A1),1)="0","",MID(TEXT($A1,"00000"),COLUMN(
A1),1)+0)

Copy B1 across to F1, fill down

 

[Michael]

Max

Thanks for this I am on my way further forward now, however this has now
brought the problem of the decimal point

The data I receive has the decimal point in, when I enter 10.75 is displayed
for example

It returns the values of 00011, have tried using place but made no
difference

Any Ideas

TIA

Michael

 

[Max]

Perhaps try instead in B1:
=IF(MID(TEXT($A1,"00.00"),COLUMN(A1),1)=".",".",
MID(TEXT($A1,"00.00"),COLUMN(A1),1)+0)
Copy B1 across to F1, fill down as before

--

 

[Michael]

Max

Thanks for your help on this one, and apologies for the delay on responding,
have not had the time to look at suggestion until now.

However I have tried this and it spilt up the data, your first suggestion
was closer, however I am not grasping what you are applying

I basically get decimal entries that I can Vlookup from another worksheet in
one column
ie
1.5
10.75
100.25
etc

and I would like the respective digit placed into a column and the decimal
point would be disguarded
and then display is over five columns

ie a 1.5 decimel entry in the column would display as

|0|0|1|5|0|

10.75

|0|1|0|7|5|

100.25

|1|0|0|2|5|

Can you advise any further or is this not a functional

MTIA

Michael

 

[Dave Peterson]

With the data in A1, put this in B1 and drag across to F1
=MID(TEXT($A1*100,"00000"),COLUMN()-1,1)

Then select B1:F1 and drag down as far as you need.

Alternatively...

You could put this in B1:
=text(a1*100,"00000")
then drag down column B.
Select column B
edit|copy
edit|paste special|values

Then use data|text to columns.
choose fixed width and draw a line between each digit.

 

[Max]

This slight revision to the first suggestion should do it, hopefully ..

Sample construct at:
http://cjoint.com/?mhcG44L4dP
SplittingFormulaReturnsIntoCells_Michael_misc.xls

With the returns from the VLOOKUP in A1 down,

Put in B1:
=IF(MID(TEXT($A1*100,"00000"),COLUMN(A1),1)="0",0,
MID(TEXT($A1*100,"00000"),COLUMN(A1),1)+0)

Copy B1 across to F1, fill down

"1.5" value returned by VLOOKUP in A1 would display as:
|0|0|1|5|0| in B1:F1

"10.75" as:
|0|1|0|7|5|

"100.25" as:
|1|0|0|2|5|

and so on ..

--