 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007Extract text from a string
I have the following cells A1 = Joe Bloggs[20%] A2 = Fred Astair[5%] I would like to pull the percentage out of the name into another cell. At the moment I have MID(A1,SEARCH("[",A1)+1,3) However this won't work for a percentage under 10, like 5%. Also, there are occasions when there is no percentage in brackets, just the person's name - is there a way of allowing for this without using an extra IF statement? Thanks, Ellebelle Ellebelle Well I couldn't manage it without IF statements, will this do? =IF(LEN(MID(A1,SEARCH("[",A1)+1,3))=1,"",IF(RIGHT(MID(A1,SEARCH("[",A1)+1,3),1)="]",MID(A1,SEARCH("[",A1)+1,2),MID(A1,SEARCH("[",A1)+1,3))) Mike "" wrote: > I have the following cells > [quoted text clipped - 14 lines] > > Ellebelle Hi, Try following formula... =IF(ISERROR(SEARCH("[",A1)),"",SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("[",A1),(LEN(A1)-SEARCH("[",A1))+1),"[",""),"]",""))  SignatureHaldun Alay >I have the following cells > [quoted text clipped - 16 lines] > > Ellebelle Hi Ellebelle Try =SUBSTITUTE(MID(A1,SEARCH("[",A1)+1,3),"]","") SignatureRegards Roger Govier >I have the following cells > [quoted text clipped - 17 lines] > > Ellebelle You can use TTC (Text To Columns), Delimited, With the left square bracket as the delimiter. After the primary separation, use Edit & Replace to eliminate the right square bracket. This gives you actual data, with *no* ensuing formulas to deal with. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I have the following cells A1 = Joe Bloggs[20%] A2 = Fred Astair[5%] I would like to pull the percentage out of the name into another cell. At the moment I have MID(A1,SEARCH("[",A1)+1,3) However this won't work for a percentage under 10, like 5%. Also, there are occasions when there is no percentage in brackets, just the person's name - is there a way of allowing for this without using an extra IF statement? Thanks, Ellebelle |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.