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