If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select specific text in cell
Is there a simple way to extract text in a cell that occurs between specific
characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#2
|
|||
|
|||
Select specific text in cell
Using MID comes to mind
Post some examples for better answers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John Gregory" wrote in message ... Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#3
|
|||
|
|||
Select specific text in cell
hi
you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#4
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 16:20:00 -0800, John Gregory
wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 Yes --ron |
#5
|
|||
|
|||
Select specific text in cell
The text strings are random length - file path names:
c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas "FSt1" wrote: hi you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#6
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory
wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR (1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron |
#7
|
|||
|
|||
Select specific text in cell
hi,
using your example as an example, try this.... =MID(C1,(SEARCH("\",C1,4)+1),(SEARCH("-",C1,4)-1)-SEARCH("\",C1,4)) returns "filename345" look up =search in xl help also. Regards FSt1 "John Gregory" wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas "FSt1" wrote: hi you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#8
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 21:36:13 -0500, Ron Rosenfeld
wrote: On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHA R(1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron By the way, here are some UDF's that will do the same thing. They can be entered in a regular module and then used as a function. To enter into a regular module, alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste one of the codes below into the window that opens: ============================================= Option Explicit Function fn(str As String) As String Dim s1() As String Dim s2() As String s1 = Split(str, "\") s2 = Split(s1(UBound(s1)), "-") fn = Trim(s2(LBound(s2))) End Function ========================================== The above as a "one-liner" in deference to Rick: ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== and using Regular Expressions, which, although a bit longer, took a fraction of the time to develop and test of any of the other solutions. ================================ Option Explicit Function fn(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\\([^\-\\]*\S)\s?-[^\\]*$" If re.test(str) = True Then Set mc = re.Execute(str) fn = mc(0).submatches(0) End If End Function ================================= --ron |
#9
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 18:40:00 -0800, FSt1
wrote: hi, using your example as an example, try this.... =MID(C1,(SEARCH("\",C1,4)+1),(SEARCH("-",C1,4)-1)-SEARCH("\",C1,4)) returns "filename345" look up =search in xl help also. Regards FSt1 Your routine will only return just the filename only if filename is in a folder in the root directory; and also if there are no "-" in the folder or filenames. For example: c:\sampledir1\subfolder1\filename - description your formula returns: subfolder1\filename OR c:\samplelongname2\filename-345 - longer description where it will only return filename and not filename-345 --ron |
#10
|
|||
|
|||
Select specific text in cell
That works! Thank you very much
"Ron Rosenfeld" wrote: On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR (1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron |
Thread Tools | |
Display Modes | |
|
|