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
