Home 
Search 
Today's Posts 
#11




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




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




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




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




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




How to insert a value in one cell into many cells
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://jwalk.com/ss/excel/index.htm Tushar Mehta's: http://www.tusharmehta.com/ Jon Peltier's: http://www.geocities.com/jonpeltier/ Ron de Bruin's [Google search AddIn]: 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 
#17




How to insert a value in one cell into many cells
Oops ..
Bob Phillip's: should read: Bob Phillips':  Rgds Max xl 97  Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895  
#18




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