 |
 | 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 / New Users / September 2006Extracting text from a string.
Hello, Is there a way for excel to extract a snippet of data from a text string? For the following string, Form:FOUR:OL_FRYEN:00:Text:ON I want to extract the data between the 2nd and 3rd colons, so the result should be: OL_FRYEN The data is always in Column A, so ideally, the extracted bit would be entered beside the source string, in Column B. Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30 to 6000 lines, and I want to extract the data between the 2nd and 3rd ":" in each case. I started to do this using a formula but it quickly got very cumbersome. Can a Macro do this? Or is there a formula I can use? Thank you for your assistance, Art. Is there *always* at least 3 colons in each string? Have you considered using Text to Columns which will parse the string into segments based on the colon as a delimiter? Biff > Hello, > [quoted text clipped - 21 lines] > > Art. Yes there is always at least 3 colons in each string, and no (D'oh) I had not considered importing the text file and then parsing it with the colon as the delimiter. I have more than 250 text files to do so that seems like a lot of work. Thanks for the suggestion though, Art. > Is there *always* at least 3 colons in each string? > [quoted text clipped - 28 lines] >> >> Art. Here's another formula using the built-in functions that's several keystrokes shorter: =LEFT(MID(A1,FIND("~",SUBSTITUTE(A1,":","~",2))+1,255),FIND(":",MID(A1,FIND("~",SUBSTITUTE(A1,":","~",2))+1,255))-1) Biff > Yes there is always at least 3 colons in each string, and no (D'oh) I had > not considered importing the text file and then parsing it with the colon [quoted text clipped - 38 lines] >>> >>> Art. >Hello, > [quoted text clipped - 21 lines] > >Art. Several methods: It can certainly be done with a formula using built-ins: =MID(A1,FIND(CHAR(1),SUBSTITUTE( A1,":",CHAR(1),2))+1,FIND(CHAR(1), SUBSTITUTE(A1,":",CHAR(1),3))-FIND( CHAR(1),SUBSTITUTE(A1,":",CHAR(1),2))-1) You can use a simpler formula if you download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ and then use the Regular Expression formula: =REGEX.MID(A1,"[^:]+",3) You'll also get a bunch of other useful functions, which can be embedded in your worksheet if it is to be distributed. Finally, you can select Data/Text to Columns and use ":" as the delimiter. Then delete the non-relevant columns. This latter could be recorded as a macro. --ron >>Hello, >> [quoted text clipped - 48 lines] > > --ron Yippee, =MID(A1,FIND(CHAR...) did the trick. I've downloaded the morefunc.exe as well. Thanks you, Art. >>>Hello, >>> [quoted text clipped - 55 lines] > >Art. You're welcome. Glad to help. Thanks for the feedback. --ron
|
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.