I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started
from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)
instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:
I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started
from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)
instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:

One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:
I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started
from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)
instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Hi Thomas

What is the value of cell A1? Try entering 123456789 in A1 to see the
result.

Regards
Rowan

Thomas wrote:
Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:


One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:

I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started
from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)
instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

"Rowan Drummond" wrote:

Hi Thomas

What is the value of cell A1? Try entering 123456789 in A1 to see the
result.

Regards
Rowan

Thomas wrote:
Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:


One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:

I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started
from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)
instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Hi Thomas

All my cells are formatted as General and it works for me with 125000000
in A1.

Try inserting a new sheet, type 125000000 in A1, in B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
Then copy B1 across to J1.
Works for me.

Regards
Rowan

Thomas wrote:
Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

"Rowan Drummond" wrote:


Hi Thomas

What is the value of cell A1? Try entering 123456789 in A1 to see the
result.

Regards
Rowan

Thomas wrote:

Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:



One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:


I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started

from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)

instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Perhaps you could be having text instead of numbers in A1
(The A1 sample in your orig. post even had
spaces in between the digit "0" !)

Try this slight revision to Rowan's formula in B1,
copy across to J1 as befo

=IF(ISERROR(MID(SUBSTITUTE($A1," ","")+0,COLUMN()-1
+LEN(SUBSTITUTE($A1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($A1," ","")+0,COLUMN()-1
+LEN(SUBSTITUTE($A1," ","")+0)-9,1))

And if required, just select B1:J1 and fill down to return correspondingly
if you have other values in A2, A3 ...
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Thomas" wrote in message
...
Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must
also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

Dear Rowan,

Yes it works, on the new worksheet, thank you

regards,

Thomas

"Rowan Drummond" wrote:

Hi Thomas

All my cells are formatted as General and it works for me with 125000000
in A1.

Try inserting a new sheet, type 125000000 in A1, in B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
Then copy B1 across to J1.
Works for me.

Regards
Rowan

Thomas wrote:
Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

"Rowan Drummond" wrote:


Hi Thomas

What is the value of cell A1? Try entering 123456789 in A1 to see the
result.

Regards
Rowan

Thomas wrote:

Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:



One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:


I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started

from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)

instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Dear Rowan,

Do you have any references (book, etc) for me to learn this kind of stuff

Cheers

"Rowan Drummond" wrote:

Hi Thomas

All my cells are formatted as General and it works for me with 125000000
in A1.

Try inserting a new sheet, type 125000000 in A1, in B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
Then copy B1 across to J1.
Works for me.

Regards
Rowan

Thomas wrote:
Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

"Rowan Drummond" wrote:


Hi Thomas

What is the value of cell A1? Try entering 123456789 in A1 to see the
result.

Regards
Rowan

Thomas wrote:

Dear Rowan,
I wrote the formula below as instructed but it does nothing, it just give a
blank cell with no values at all.

"Rowan Drummond" wrote:



One way:

In B1 enter:
=IF(ISERROR(MID($A$1,COLUMN()-1+LEN($A$1)-9,1)),"",MID($A$1,COLUMN()-1+LEN($A$1)-9,1))
and copy across to J1.

Hope this helps
Rowan

Thomas wrote:


I am currently creating a formula/function to insert a value in once cell
into many cells using excel 2003 , for example: cell A1 has a value 100 000
000, and the value is going to be inserted, into
B1,C1,D1,E1,F1,G1,H1,I1,J1.Each cell will only have one digit only
so B1 will have 1, C1 will have 0, D1 will have 0 and for the rest of the
cell will have the same thing instead of whole value.I tried this thing with
the MID function and it works as long as the source value cell is 9
digits(filling the cell from the B1toJ1), it became my concern when I changed
those value less then 9 digits,say 8 or 5 digits where the cell will receive
the value are B1 to F1 if 5 digits or B1 to I1 if 8 digits, the rest of the
cells are blank.
Are there any possibilities, solutions if the inserted value can be started

from C1 to J1 for 8 digits or F1 to J1 for 5 digits etc (less than 9 digits)

instead of B1 to I1 (8 digits) or B1 to F1 for 5 digits?
It would be great help and apprecition for any solution from you
thanks
Thomas

Hi Max,

Just try to figure out why the formula shows nothing (blank cell) when It
copied to another cells such as L1 or AA1 (it does not show any result)

Thanks

"Max" wrote:

Perhaps you could be having text instead of numbers in A1
(The A1 sample in your orig. post even had
spaces in between the digit "0" !)

Try this slight revision to Rowan's formula in B1,
copy across to J1 as befo

=IF(ISERROR(MID(SUBSTITUTE($A1," ","")+0,COLUMN()-1
+LEN(SUBSTITUTE($A1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($A1," ","")+0,COLUMN()-1
+LEN(SUBSTITUTE($A1," ","")+0)-9,1))

And if required, just select B1:J1 and fill down to return correspondingly
if you have other values in A2, A3 ...
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Thomas" wrote in message
...
Rowan,

The A1 cell's value is 125000000, I tried with the suggested number and
still not showing any changes at all. Does the format of the cell must
also
be set into specific format such as number, text, general , etc as to make
the formula work ?

Thanks

Thomas

Hi Thomas

Glad I could help.

I have some books by John Walkenbach on excel programming but nothing on
worksheet functions. There are some good resources on the internet. Some
that spring to mind a
www.cpearson.com
www.contextures.com
www.xldynamic.com
www.j-walk.com

Good luck
Rowan

Thomas wrote:
Dear Rowan,

Do you have any references (book, etc) for me to learn this kind of stuff

Cheers

"Rowan Drummond" wrote:

"Thomas" wrote:
Just try to figure out why the formula shows nothing
(blank cell) when it is copied to another cells
such as L1 or AA1 (it does not show any result)

Simply because MID(...) would return: "" (empty text)
as the start_num* would evaluate to a figure greater
than the length of text in A1

*start_num part:
... COLUMN()-1+LEN(SUBSTITUTE($A1," ","")+0)-9 ..

The "max" length of the value in A1 was given in the orig. post as 9 digits,
so the decomposition by the formula was across a max of 9 cols (from the
starting cell B1 copied across to J1)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

So how to make it work on the other cell then ?

"Max" wrote:

"Thomas" wrote:
Just try to figure out why the formula shows nothing
(blank cell) when it is copied to another cells
such as L1 or AA1 (it does not show any result)

Simply because MID(...) would return: "" (empty text)
as the start_num* would evaluate to a figure greater
than the length of text in A1

*start_num part:
... COLUMN()-1+LEN(SUBSTITUTE($A1," ","")+0)-9 ..

The "max" length of the value in A1 was given in the orig. post as 9 digits,
so the decomposition by the formula was across a max of 9 cols (from the
starting cell B1 copied across to J1)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Thomas" wrote:
So how to make it work on the other cell then ?

Depending on where is your new source cell, and
where you want to decompose the digits,
the formula could be revised/adapted to suit ..

For example, if your "other" source cell is in say, K1,
and you want to decompose it within say, L1-T1

Put in L1, copy across 9 cols to T1:

=IF(ISERROR(MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)+0)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Dear Max,

It works
Thank you very much indeed for your kindness help and time,
In addition, could you kindly advise for any references especially
book,websites, etc that it might help me to learn or mastering this kind of
matter.
It would be greatly appreciated for your advise and help

Regards,

Thomas


"Max" wrote:

"Thomas" wrote:
So how to make it work on the other cell then ?

Depending on where is your new source cell, and
where you want to decompose the digits,
the formula could be revised/adapted to suit ..

For example, if your "other" source cell is in say, K1,
and you want to decompose it within say, L1-T1

Put in L1, copy across 9 cols to T1:

=IF(ISERROR(MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)),"",
MID(SUBSTITUTE($K1," ","")+0,COLUMNS($K$1:K1)
+LEN(SUBSTITUTE($K1," ","")+0)-9,1)+0)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

well.. here's a couple of links** you may want to check out ..
(Listing is certainly *not* exhaustive, and in no particular order .. )
**some are mentioned in Rowan's response to you

David McRitchie's .. :
http://www.mvps.org/dmcritchie/excel....htm#tutorials
http://www.mvps.org/dmcritchie/excel...tm#vbtutorials

Debra Dalgleish's:
http://www.contextures.com/tiptech.html
Her page: http://www.contextures.com/xlbooks.html
houses a remarkably comprehensive Excel book list
to suit every taste !

Bob Phillip's:
http://www.xldynamic.com/source/xld.UsingMenu.html

Chip Pearson's:
http://www.cpearson.com/excel/topic.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/index.html

John Walkenbach's:
http://j-walk.com/ss/excel/index.htm

Tushar Mehta's:
http://www.tushar-mehta.com/

Jon Peltier's:
http://www.geocities.com/jonpeltier/

Ron de Bruin's [Google search Add-In]:
http://www.rondebruin.nl/Google.htm

And .. don't forget these wonderful excel newsgroups <bg
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Thomas" wrote in message
...
Dear Max,

It works
Thank you very much indeed for your kindness help and time,
In addition, could you kindly advise for any references especially
book,websites, etc that it might help me to learn or mastering this kind
of
matter.
It would be greatly appreciated for your advise and help

Regards,

Thomas

Oops ..

Bob Phillip's:

should read:

Bob Phillips':

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

and some updates / clarifications ..

Jon Peltier's new homepage is at:
http://peltiertech.com/

Ron de Bruin's homepage is at:
http://www.rondebruin.nl

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--