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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Michael" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Michael" <[email protected]> wrote in message
> news:[email protected]...
>> 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]

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Michael" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Singapore, GMT+8
> > xdemechanik
> > http://savefile.com/projects/236895
> > --
> > "Michael" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

[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

"Max" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Michael" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > --
>> > Rgds
>> > Max
>> > xl 97
>> > ---
>> > Singapore, GMT+8
>> > xdemechanik
>> > http://savefile.com/projects/236895
>> > --
>> > "Michael" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[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.



Michael wrote:
>
> 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
>
> "Max" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Singapore, GMT+8
> > xdemechanik
> > http://savefile.com/projects/236895
> > --
> > "Michael" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> > --
> >> > Rgds
> >> > Max
> >> > xl 97
> >> > ---
> >> > Singapore, GMT+8
> >> > xdemechanik
> >> > http://savefile.com/projects/236895
> >> > --
> >> > "Michael" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >

--

Dave Peterson

[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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Michael" <[email protected]> wrote in message
news:[email protected]...
> 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