Hm, for some reason I only received this email after I send the last one. Ashish's solution is much more elegant than mine.
Please forgive for sending useless attachments to your emails. On Sun, Aug 29, 2010 at 5:32 AM, ashish koul <koul.ash...@gmail.com> wrote: > check the attachment if it helps > > On Sun, Aug 29, 2010 at 1:21 PM, Dave Bonallack <davebonall...@hotmail.com > > wrote: > >> Hi, >> Unfortuneately, your data is not as it appears. On the workbook you >> posted, select cell B5. >> Look in the formula bar (just above the Column headers) >> You will see: *180.5* even though the cell displays *180.500 kg* >> This is because custom cell formatting in B5 puts in the extra info. >> Now, in cell E5, put '=B5' (without the quotes) >> You will see that just the value from B5 is returned, since Cell E5 >> doesn't have the custom formatting. >> So it's easy to get the values you want, but hard to get the units. >> Regards - Dave. >> ------------------------------ >> Date: Sun, 29 Aug 2010 12:41:21 +0530 >> >> Subject: Re: $$Excel-Macros$$ Help in Separate the Num & Alpha in a cell >> into another two cells >> From: ankit.agrawal...@gmail.com >> To: excel-macros@googlegroups.com >> >> I did not get it what does it means? i m newly in excel, so plz decribe it >> >> thanks & regards >> ankit >> >> On Sun, Aug 29, 2010 at 10:14 AM, Dave Bonallack < >> davebonall...@hotmail.com> wrote: >> >> Hi, >> You have custom formatting in the both of your main data columns, which >> means that only the numeric value is entered. (Select one of the cells and >> see what appears in the formula bar). The units are done with the >> formatting. So you only need a simple =A2 sort of formula in a cell with >> general formatting to return the value. Returning the unit can't be done >> with worksheet formulas, I don't think. You'll need VBA to copy the >> formatting of each cell to the new column, which will display the unit. >> Regards - Dave. >> >> ------------------------------ >> Date: Sun, 29 Aug 2010 09:31:23 +0530 >> Subject: Re: $$Excel-Macros$$ Help in Separate the Num & Alpha in a cell >> into another two cells >> From: ankit.agrawal...@gmail.com >> To: excel-macros@googlegroups.com >> >> >> I am attaching a sample file, in this file, there are two column one for >> qty & second one for the rate. I have to separate the qty column in qty & >> its unit & again in rate column.I have written in attach copy what format i >> required in the sheet. >> >> thanks & regards >> >> Ankit Agrawal >> >> On Fri, Aug 27, 2010 at 11:29 AM, C.G.Kumar >> <kumar.bemlmum...@gmail.com>wrote: >> >> Could you please attach sample file. >> >> Thanks & Regards, >> >> >> C.G.Kumar >> >> >> On Fri, Aug 27, 2010 at 10:21 AM, Ankit Agrawal < >> ankit.agrawal...@gmail.com> wrote: >> >> Hello Sir, >> >> I have a data in which a column having the both numeric and alphabetically >> value but i want to separate these value into two column or two cells, is it >> possible in the excel if yes how can it possible? basically it is in the >> Price column and it having unit name also. so i want to separate in unit >> name and the price. >> >> plz consider it and tell me. >> >> Thanks to you all. >> >> Ankit Agrawal >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 7000 subscribers worldwide and receive many nice notes about >> the learning and support from the group.Let friends and co-workers know they >> can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 7000 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe