I got below data in Range("A1:A4")

C:\Documents\Deal\Tony Jayes.xlsm
C:\Documents\Records\John Smith (Survey Form).xlsm
C:\Documents\Project\Michael Taylor - MCC.xlsm
C:\Documents\Records\Simon Craig (FTT).xlsm

I need some formula or macro to get result in Range("B1:B4") like
below

Tony Jayes
John Smith
Michael Taylor
Simon Craig

Basically I want names to be extracted from column A Text. Please
can anyone can help

Assuming your names are **always** made up of two parts (a first and last
name) and that they are **always** followed by either a dot-extension (.xlsm
in your example) or a space (separating it from the non name parts), then
this macro should do what you want...

Sub GetNames()
Dim Cell As Range, Text As String, Parts() As String
For Each Cell In Range("A1:A4")
Text = Replace(Split(Cell, "\")(UBound(Split(Cell, "\"))), ".", " ")
Parts = Split(Text, " ", 3)
Parts(2) = ""
Cell.Offset(0, 1).Value = Trim(Join(Parts))
Next
End Sub

--
Rick (MVP - Excel)


"K" wrote in message
...
I got below data in Range("A1:A4")

C:\Documents\Deal\Tony Jayes.xlsm
C:\Documents\Records\John Smith (Survey Form).xlsm
C:\Documents\Project\Michael Taylor - MCC.xlsm
C:\Documents\Records\Simon Craig (FTT).xlsm

I need some formula or macro to get result in Range("B1:B4") like
below

Tony Jayes
John Smith
Michael Taylor
Simon Craig

Basically I want names to be extracted from column A Text. Please
can anyone can help

Hi,

If your data are consistent as in your posted examples, this works and you
don't need a macro. Put it in B1 and drag down

=IF(ISERROR(MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,"\", CHAR(7),3))+1,FIND(CHAR(7),SUBSTITUTE(A1,"
",CHAR(7),2))-FIND(CHAR(7),SUBSTITUTE(A1,"\",CHAR(7),3))-1)),MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,"\",CHAR(7), 3))+1,FIND(CHAR(7),SUBSTITUTE(A1,".",CHAR(7),1))-FIND(CHAR(7),SUBSTITUTE(A1,"\",CHAR(7),3))-1),MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,"\",CHAR(7),3 ))+1,FIND(CHAR(7),SUBSTITUTE(A1,"
",CHAR(7),2))-FIND(CHAR(7),SUBSTITUTE(A1,"\",CHAR(7),3))-1))

Mike

"K" wrote:

I got below data in Range("A1:A4")

C:\Documents\Deal\Tony Jayes.xlsm
C:\Documents\Records\John Smith (Survey Form).xlsm
C:\Documents\Project\Michael Taylor - MCC.xlsm
C:\Documents\Records\Simon Craig (FTT).xlsm

I need some formula or macro to get result in Range("B1:B4") like
below

Tony Jayes
John Smith
Michael Taylor
Simon Craig

Basically I want names to be extracted from column A Text. Please
can anyone can help
.

On Wed, 28 Oct 2009 08:09:07 -0700 (PDT), K wrote:

I got below data in Range("A1:A4")

C:\Documents\Deal\Tony Jayes.xlsm
C:\Documents\Records\John Smith (Survey Form).xlsm
C:\Documents\Project\Michael Taylor - MCC.xlsm
C:\Documents\Records\Simon Craig (FTT).xlsm

I need some formula or macro to get result in Range("B1:B4") like
below

Tony Jayes
John Smith
Michael Taylor
Simon Craig

Basically I want names to be extracted from column A Text. Please
can anyone can help

Here is a UDF (User Defined Function):

===========================
Option Explicit
Function ExtractNames(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = ".*?([^\\]+?)(?=\s*[\-(.]).*"
ExtractNames = re.Replace(s, "$1")
End Function
================================
--ron

On Wed, 28 Oct 2009 16:29:39 -0400, Ron Rosenfeld
wrote:

On Wed, 28 Oct 2009 08:09:07 -0700 (PDT), K wrote:

I got below data in Range("A1:A4")

C:\Documents\Deal\Tony Jayes.xlsm
C:\Documents\Records\John Smith (Survey Form).xlsm
C:\Documents\Project\Michael Taylor - MCC.xlsm
C:\Documents\Records\Simon Craig (FTT).xlsm

I need some formula or macro to get result in Range("B1:B4") like
below

Tony Jayes
John Smith
Michael Taylor
Simon Craig

Basically I want names to be extracted from column A Text. Please
can anyone can help

Here is a UDF (User Defined Function):

===========================
Option Explicit
Function ExtractNames(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = ".*?([^\\]+?)(?=\s*[\-(.]).*"
ExtractNames = re.Replace(s, "$1")
End Function
================================
--ron

Just a note on limitations that I neglected to enter before.

Based on your examples, this UDF looks for the strings between the last "\" and
the ".". However, it also assumes that if there is some "non-name" information
in that area (e.g. - MCC; (FTT), etc) that this part will begin with a hyphen
or "(". Accordingly, it will not include the latter part of any hyphenated
names.
--ron