Home 
Search 
Today's Posts 
#1




How to insert a value in one cell into many cells
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 
#2




How to insert a value in one cell into many cells
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 
#3




How to insert a value in one cell into many cells
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 
#4




How to insert a value in one cell into many cells
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 
#5




How to insert a value in one cell into many cells
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 
#6




How to insert a value in one cell into many cells
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 
#7




How to insert a value in one cell into many cells
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 
#8




How to insert a value in one cell into many cells
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 
#9




How to insert a value in one cell into many cells
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 
#10




How to insert a value in one cell into many cells
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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Conditional Cell Shading (based on the shading of other cells)  Excel Worksheet Functions  
copying cell names  Excel Discussion (Misc queries)  
Maintaining cell references when inserting cells elsewhere  Excel Discussion (Misc queries)  
How do I make a cell equal to another cells value and not it's fo.  Excel Worksheet Functions  
Convert data of cells to any type: Number, Date&Time, Text  Excel Discussion (Misc queries) 