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

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

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

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

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

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

On Fri, 15 Feb 2008 22:00:45 -0500, Ron Rosenfeld
wrote:

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
=================================

OF course, the regular expression variation as posted above is wrong <g.
Should read:

====================================
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

The above as a "one-liner" in deference to Rick:

LOL

============================================
Function fn(str As String) As String
fn = Trim(Split(Split(str, "\")(UBound(Split _
(str, "\"))), "-")(LBound(Split(Split _
(str, "\")(UBound(Split(str, "\"))), "-"))))
End Function
==========================================

The LBound for a Split is always 0 no matter what the Option Base is set to.
Using this fact, your one-liner can be simplified considerably...

Function fn(str As String) As String
fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0))
End Function

Rick

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

On Fri, 15 Feb 2008 19:14:01 -0800, John Gregory
wrote:

That works! Thank you very much

You're welcome. Thanks for the feedback.
--ron

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

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

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