Very Good! On Sat, Jul 3, 2010 at 2:15 PM, ashish koul <koul.ash...@gmail.com> wrote:
> Thanks a lot Dave..... > > > > On Sat, Jul 3, 2010 at 2:22 PM, Dave Bonallack > <davebonall...@hotmail.com>wrote: > >> Hi, >> A1 = *United States of America* >> >> =FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," >> ","")))) >> Firstly, we need to find out how many spaces there are in the text. We do >> this by finding the text length of the original text, *Len(TRIM(A1))*then >> the text length of the text with all the spaces removed >> *LEN(SUBSTITUTE(A1," ",""))*. Then find the difference >> *LEN(TRIM(A1))*-*LEN(SUBSTITUTE(A1," >> ",""))*. In our case, 3. So the last instance of the space character is >> the 3rd one. >> The SUBSTITUTE function takes 4 arguements. 1: The original text, 2: text >> string you don't want, 3: text string you want instead, and 4: the optional >> arguement of what instance. So in our case, we want to replace the 3rd >> instance of the space character with something (anything) unique; for this, >> we use CHAR(1) (whatever that is). *SUBSTITUTE(A1," ",CHAR(1),3)* >> So replacing the 3rd instance of the space character with Char(1) we get >> *United States ofCHAR(1)America* >> Now we just use the FIND function to return the position of the CHAR(1) >> character. >> >> Cool Huh! >> >> Regards - Dave. >> >> >> ** >> ** >> >> ------------------------------ >> Date: Sat, 3 Jul 2010 10:52:12 +0530 >> Subject: Re: $$Excel-Macros$$ Fwd: >> From: koul.ash...@gmail.com >> >> To: excel-macros@googlegroups.com >> >> hi dave >> >> can you explain the formula how it is working >> >> =FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(TRIM(A1))-LEN( >> SUBSTITUTE(A1," ","")))) >> >> >> On Fri, Jul 2, 2010 at 10:29 PM, praveen kumar <bhspra...@gmail.com>wrote: >> >> Here with have attached one pdf file. from that if i copy paste data >> to excel then it will show country name & numbers together like this >> >> 1 United States 14,093,310 >> >> 2 China 7,909,261 >> >> I want to seprate this country names & numbers here. If have found >> out the position of the space from the right can use mid & other >> formula to get the numbers separately. >> >> I hope this will makes you clear about my objective. >> >> Also let me if there is any other way to do the same. >> >> Thanks in advance >> >> On Fri, Jul 2, 2010 at 9:38 PM, krishna mummina <lovemekris...@gmail.com> >> wrote: >> > Hi praveen, >> > >> > we can find the position and could you please tell me why you need it. >> > >> > Bye >> > >> > On 7/2/10, praveen kumar <bhspra...@gmail.com> wrote: >> >> Hi Friends, >> >> >> >> Is there any way to find the position of the space in a word from right >> >> side. >> >> >> >> Ex: United States of America - here the position of the space from >> >> right side is 8. >> >> >> >> >> >> -- >> >> >> >> Regards, >> >> praveen >> >> >> >> -- >> >> >> ---------------------------------------------------------------------------------- >> >> 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 >> > >> >> >> >> -- >> >> >> >> Regards, >> praveen >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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 >> >> ------------------------------ >> Australia's #1 job site If It Exists, You'll Find it on >> SEEK<http://clk.atdmt.com/NMN/go/157639755/direct/01/> >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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
