Select specific text in cell

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

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

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
 
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
 
That works! Thank you very much

Ron Rosenfeld said:
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
 
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
 
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


Nice

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

And you've told me that before <grrr> <slap upside my head>
--ron
 
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

Actually, neither your one liner nor my longer variants will work if filename
includes a "-". For that, we need something like:

==============================
Option Explicit
Function fn(str As String) As String
Dim s1() As String
s1 = Split(str, "\")
s1 = Split(s1(UBound(s1)), "-")
ReDim Preserve s1(UBound(s1) - 1)
fn = Trim(Join(s1, "-"))
End Function
===========================
--ron
 
Ron Rosenfeld said:
Actually, neither your one liner nor my longer variants will work if
filename
includes a "-". For that, we need something like:

==============================
Option Explicit
Function fn(str As String) As String
Dim s1() As String
s1 = Split(str, "\")
s1 = Split(s1(UBound(s1)), "-")
ReDim Preserve s1(UBound(s1) - 1)
fn = Trim(Join(s1, "-"))
End Function
===========================

I thought I had corrected it as per your observation after the OP posted his
sample text line ... use " - ", not "-", in the one-liner...

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

Of course, this supposes the filename itself does not contain dash
surrounded by spaces.

Rick
 
Actually, neither your one liner nor my longer variants will work if
filename
includes a "-". For that, we need something like:

==============================
Option Explicit
Function fn(str As String) As String
Dim s1() As String
s1 = Split(str, "\")
s1 = Split(s1(UBound(s1)), "-")
ReDim Preserve s1(UBound(s1) - 1)
fn = Trim(Join(s1, "-"))
End Function
===========================

And, of course, the above would not work if the description itself contained
a dash.

Rick
 
Actually, neither your one liner nor my longer variants will work if
And, of course, the above would not work if the description itself
contained a dash.

In thinking a little more about this question, it would appear, given the
structure the OP has adopted, that **at least one** of the following must be
true or the OP cannot have a fool-proof parser... the filename can never
have a dash, or it can never have a space/dash/space combination in it, or
it can never have just a plain space in it (which would further require a
space always be present after the filename), or the description cannot have
a backslash in it... one of these must be true in order to create a parser
(one-liner or not) that would always work.

Rick
 
In thinking a little more about this question, it would appear, given the
structure the OP has adopted, that **at least one** of the following must be
true or the OP cannot have a fool-proof parser... the filename can never
have a dash, or it can never have a space/dash/space combination in it, or
it can never have just a plain space in it (which would further require a
space always be present after the filename), or the description cannot have
a backslash in it... one of these must be true in order to create a parser
(one-liner or not) that would always work.

Rick

That sounds correct.

And, at least for the parser's I've offered, the filename must be preceded by a
"\"
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top