I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other useless
info, and doesnt follow the same format) from a cell that matches the names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.
Thank You.

I'll give you some formulas to work with and see if they help. I have
separated the formulas so you can see what they do and then I put them all
together in cell A5 as one nested formula. Note that SEARCH is not case
sensitive. FIND is the same but is case sensitive.

SEARCH finds the start of the search text in the text being searched.
LEN returns the length of the search text
MID returns the search text from the start position for its length.

Cell A1: 3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
Cell A2: taylor, george
Cell A3: =SEARCH(A2,A1) this formula gives result 18
Cell A4: =LEN(A2) this formula gives result 14
Cell A5: =MID(A1,SEARCH(A2,A1),LEN(A2)) result is TAYLOR, GEORGE

Hope it helps.

Regards,

OssieMac



"NunRacer" wrote:

I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other useless
info, and doesnt follow the same format) from a cell that matches the names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.
Thank You.

"NunRacer" wrote...
....
so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.

If the names would always be the 3rd and 4th tokens (sequences of nonspace
characters separated by space characters), the general approach would be
something like

=MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),2))+1,
FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),4))
-FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),2)))

If names weren't reliably the 3rd and 4th tokens but they did match entries
in a database table exactly in terms of spelling but not necessarily in
terms of upper/lower case, and if the table of names were named TBL, you
could use a lookup formula like

=LOOKUP(2,1/COUNTIF(TRIM(A1),"*"&TBL&"*"),TBL)

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.

If the names would always be the 3rd and 4th tokens (sequences of nonspace
characters separated by space characters), the general approach would be
something like

=MID(TRIM(A1),FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),2))+1,
FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),4))
-FIND(CHAR(127),SUBSTITUTE(TRIM(A1)," ",CHAR(127),2)))

Here is a slightly shorter formula (there, that should spur you on to find
an even shorter one<g) based on the same basic assumption that the last
name is always the third token but with the additional assumption that the
last and first names are always separated by a comma-space pair...

=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2-FIND(",
",A1))&MID(A1,FIND(" ",A1,1+FIND(" ",A1)),FIND(", ",A1)-FIND(" ",A1,1+FIND("
",A1)))

Rick

"Rick Rothstein (MVP - VB)" wrote...
....
Here is a slightly shorter formula . . .
....
=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&MID(A1,FIND(" ",A1,1+FIND(" ",A1)),FIND(", ",A1)
-FIND(" ",A1,1+FIND(" ",A1)))

It won't be shorter when you wrap each A1 reference inside TRIM. Safer to
use TRIM(A1) than just A1.

"Harlan Grove" wrote...
"Rick Rothstein (MVP - VB)" wrote...
...
Here is a slightly shorter formula . . .
...
=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&MID(A1,FIND(" ",A1,1+FIND(" ",A1)),FIND(", ",A1)
-FIND(" ",A1,1+FIND(" ",A1)))

It won't be shorter when you wrap each A1 reference inside TRIM. Safer to
use TRIM(A1) than just A1.

Then again, if you assume there'd only ever be ", " between last name and
first name, you could use

=REPLACE(LEFT(TRIM(A1),FIND(" ",TRIM(A1),FIND(", ",TRIM(A1))+2)-1),
1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

or putting the result in first-name last-name order

=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

Here is a slightly shorter formula . . .
...
=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&MID(A1,FIND(" ",A1,1+FIND(" ",A1)),FIND(", ",A1)
-FIND(" ",A1,1+FIND(" ",A1)))

It won't be shorter when you wrap each A1 reference inside TRIM. Safer to
use TRIM(A1) than just A1.

Then again, if you assume there'd only ever be ", " between last name and
first name, you could use

=REPLACE(LEFT(TRIM(A1),FIND(" ",TRIM(A1),FIND(", ",TRIM(A1))+2)-1),
1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

or putting the result in first-name last-name order

=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

That formula seems to still be dependent on token numbers... let's remove
that dependency. This formula **only** requires the last and first names to
be separated by the first comma-space pair in the string...

=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2-FIND(", ",A1))&"
"&REPLACE(SUBSTITUTE(LEFT(" "&A1,1+FIND(", ",A1)-1),"
",CHAR(1),LEN(LEFT(A1,FIND(", ",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(",
",A1)))," ",""))+1),1,FIND(CHAR(1),SUBSTITUTE(LEFT(" "&A1,1+FIND(",
",A1)-1)," ",CHAR(1),LEN(LEFT(A1,FIND(",
",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1)))," ",""))+1)),"")

Now, I am pretty sure I have some excess function calls in here, but it is
5:30 AM here and I am going to sleep. If you haven't removed the excess for
me when I return, I'll attempt it when I get up. Oh, and I tried this
formula with multiple spaces and it looks like it doesn't need any TRIM
function calls. Okay, good night, I'll "see" you tomorrow.

Rick

"Rick Rothstein (MVP - VB)" wrote...
....
=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

That formula seems to still be dependent on token numbers...

Token numbers? You mean 255? It's an arbitrarily large number, at least
compared to the string length of individual names in most human languages,
and it's the 3rd argument to MID, effectively meaning get the rest of the
string. The other numbers, +1 and +2, move past the found substrings from
the FIND calls. Hardly dependencies.

Or do you mean assuming the name is the 3rd and 4th tokens combined, which
are separated by ", "?

. . . This formula **only** requires the last and first names to
be separated by the first comma-space pair in the string...

=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&" "&REPLACE(SUBSTITUTE(LEFT(" "&A1,
1+FIND(", ",A1)-1)," ",CHAR(1),LEN(LEFT(A1,FIND(", ",A1)))
-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1)))," ",""))+1),1,
FIND(CHAR(1),SUBSTITUTE(LEFT(" "&A1,1+FIND(", ",A1)-1)," ",CHAR(1),
LEN(LEFT(A1,FIND(", ",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1))),
" ",""))+1)),"")
....

If you want to use ", " as the indicator of where the name is, taking the
tokens to the left and right of it no matter where they appear in A1, and
swapping their order, use

=TRIM(MID(A1,FIND(", ",A1)+2,FIND(" ",TRIM(MID(A1,FIND(", ",A1)+2,
255)))))&MID(LEFT(A1,FIND(", ",A1)-1),FIND(", ",SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ",", ",FIND(", ",A1)-LEN(SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ","")))),255)

Somewhere in my workbook (it matter not where) I have a range called Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other useless
info, and doesnt follow the same format) from a cell that matches the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.
Thank You.

I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other useless
info, and doesnt follow the same format) from a cell that matches the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the existing
names in the database.
The end result needs to be just their name.
Thank You.

Okay, I'm awake again... see inline comments.

=LEFT(MID(TRIM(A1),FIND(", ",TRIM(A1))+2,255),FIND(" ",MID(TRIM(A1),
FIND(", ",TRIM(A1))+2,255)))&REPLACE(LEFT(TRIM(A1),
FIND(", ",TRIM(A1))-1),1,FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1),"")

That formula seems to still be dependent on token numbers...

Or do you mean assuming the name is the 3rd and 4th tokens combined, which
are separated by ", "?

Yes, that is what I meant. My choice of the word "numbers" was not a
particularly good one. Sorry.


. . . This formula **only** requires the last and first names to
be separated by the first comma-space pair in the string...

=MID(A1,2+FIND(", ",A1),FIND(" ",A1,2+FIND(", ",A1))-2
-FIND(", ",A1))&" "&REPLACE(SUBSTITUTE(LEFT(" "&A1,
1+FIND(", ",A1)-1)," ",CHAR(1),LEN(LEFT(A1,FIND(", ",A1)))
-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1)))," ",""))+1),1,
FIND(CHAR(1),SUBSTITUTE(LEFT(" "&A1,1+FIND(", ",A1)-1)," ",CHAR(1),
LEN(LEFT(A1,FIND(", ",A1)))-LEN(SUBSTITUTE((LEFT(A1,FIND(", ",A1))),
" ",""))+1)),"")

If you want to use ", " as the indicator of where the name is, taking the
tokens to the left and right of it no matter where they appear in A1, and
swapping their order, use

Yes, that was what my tired mind was trying to convey.


=TRIM(MID(A1,FIND(", ",A1)+2,FIND(" ",TRIM(MID(A1,FIND(", ",A1)+2,
255)))))&MID(LEFT(A1,FIND(", ",A1)-1),FIND(", ",SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ",", ",FIND(", ",A1)-LEN(SUBSTITUTE(LEFT(A1,
FIND(", ",A1))," ","")))),255)

Although I don't think it will affect the OP's usage, the above formula, as
written, requires at least one blank space to proceed the last name... the
formula I posted doesn't. I'm thinking here of something like this...

TAYLOR, GEORGE 15:00 - 23:00 x etc.

Here is a modification to your formula which accounts for that condition...

=MID(A1,FIND(", ",A1)+2,FIND(" ",MID(A1,FIND(",
",A1)+2,255)))&MID(LEFT(A1,FIND(", ",A1)-1),FIND(", ",SUBSTITUTE(LEFT("
"&A1,1+FIND(", ",A1)-1)," ",", ",FIND(", ",A1)-LEN(SUBSTITUTE(LEFT("
"&A1,1+FIND(", ",A1)-1)," ","")))),255)

Yes, I removed the TRIM function calls, but if you test it, you will find
neither is unnecessary. Actually, the trailing space the first one was
suppressing is needed for the modification I made to work (it supplies the
space that is separating the two names). Anyway, my testing indicates that,
with the exception of comma-space delimiter between the names, you can put
as many spaces as you want elsewhere and the modified formula will not be
affected by it. It also looks like this formula follows the general
structure of my original attempt and is probably represents the code
reduction I thought could be applied to it (which is another way of saying I
won't be revisiting my originally posted code anymore<g).

Rick

I'm not trying to divert you from Bernard's solution, but given you said you
were sort of new to Excel, I thought you might have missed the point behind
the formulas Harlan and I have been writing back and forth about,
particularly the last one by each of us. Each of them can retrieve the names
you are looking for without the need for a separately maintained "master
list" (that is, you do not need a Mylist like from Bernard's posting). Just
put either of our last formulas in A1 and then copy them down as needed.
They will retrieve, and re-order, whatever names are contained inside the
text in column A, as long as those names are separated by a comma followed
by a single space... and they will do it automatically, without needing a
separately maintained list to check against. By the way, the only difference
between Harlan's and my last posted formulas has to do with where the names
can occur within the text... my formula (a modification of Harlan's by the
way) will allow the names to be at the beginning of the text whereas
Harlan's requires some non-name text to precede the names.

Rick


"NunRacer" wrote in message
...
I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called
Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started" with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other
useless
info, and doesnt follow the same format) from a cell that matches the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the
existing
names in the database.
The end result needs to be just their name.
Thank You.

I've tried the first more simple formulas and yes yours works better because
occasionaly i do have names with no preceding text. Also another problem is
that some names have 2 first names which really throws things off. I already
have master list of names about 400 or so that i do a vlookup and where the
final result is the person first name with the last 3 letters of their last
name eg. Joe Blo and their position in the company.
As of yet i havnt been able to get the macro to work, comes out as #name?

"Rick Rothstein (MVP - VB)" wrote:

I'm not trying to divert you from Bernard's solution, but given you said you
were sort of new to Excel, I thought you might have missed the point behind
the formulas Harlan and I have been writing back and forth about,
particularly the last one by each of us. Each of them can retrieve the names
you are looking for without the need for a separately maintained "master
list" (that is, you do not need a Mylist like from Bernard's posting). Just
put either of our last formulas in A1 and then copy them down as needed.
They will retrieve, and re-order, whatever names are contained inside the
text in column A, as long as those names are separated by a comma followed
by a single space... and they will do it automatically, without needing a
separately maintained list to check against. By the way, the only difference
between Harlan's and my last posted formulas has to do with where the names
can occur within the text... my formula (a modification of Harlan's by the
way) will allow the names to be at the beginning of the text whereas
Harlan's requires some non-name text to precede the names.

Rick


"NunRacer" wrote in message
...
I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called
Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started" with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other
useless
info, and doesnt follow the same format) from a cell that matches the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the
existing
names in the database.
The end result needs to be just their name.
Thank You.

If you are getting #NAME! error you most likely have placed the function in
the wrong place
Use Tools | Macro | VBA editor
Make sure your workbook is select in the Project window (left hand panel)
Use Insert | Module
Past my function in the module window
Still not working? Send me a private message (remove TRUENORTH)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried the first more simple formulas and yes yours works better
because
occasionaly i do have names with no preceding text. Also another problem
is
that some names have 2 first names which really throws things off. I
already
have master list of names about 400 or so that i do a vlookup and where
the
final result is the person first name with the last 3 letters of their
last
name eg. Joe Blo and their position in the company.
As of yet i havnt been able to get the macro to work, comes out as #name?

"Rick Rothstein (MVP - VB)" wrote:

I'm not trying to divert you from Bernard's solution, but given you said
you
were sort of new to Excel, I thought you might have missed the point
behind
the formulas Harlan and I have been writing back and forth about,
particularly the last one by each of us. Each of them can retrieve the
names
you are looking for without the need for a separately maintained "master
list" (that is, you do not need a Mylist like from Bernard's posting).
Just
put either of our last formulas in A1 and then copy them down as needed.
They will retrieve, and re-order, whatever names are contained inside the
text in column A, as long as those names are separated by a comma
followed
by a single space... and they will do it automatically, without needing a
separately maintained list to check against. By the way, the only
difference
between Harlan's and my last posted formulas has to do with where the
names
can occur within the text... my formula (a modification of Harlan's by
the
way) will allow the names to be at the beginning of the text whereas
Harlan's requires some non-name text to precede the names.

Rick


"NunRacer" wrote in message
...
I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called
Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started"
with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt
found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other
useless
info, and doesnt follow the same format) from a cell that matches
the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the
existing
names in the database.
The end result needs to be just their name.
Thank You.

Ah yes, two first names... I should have thought about that because I know
someone whose first name is Mary Anne.

Okay, with regard to Bernard's function... in the VB editor, place a Module
into your project (click Insert/Module from the VBA menu bar) and put his
code in the code window that appears when you add the Module. Wherever you
place the list of name to be looked up, make sure each name (last, first) is
in a single cell (that is, don't split last and first names across two or
more cells). Select the entire list of these names and name the range Mylist
(you can do that by typing it into the field to the left of the formula bar
where the current cell address is displayed). Next, place this formula

=GETNAME(A1,MyList)

in the cell you want to list the transposed names in and copy it down. That
should get it to work for you. HOWEVER, as written, Bernard's code does not
actually transpose the names, it simply repeats them. Here is Bernard's code
modified to do the transposition (I also added variable declarations... note
one of them is a dynamic array)

Function GETNAME(MyCell, MyList) As String
Dim J As Long
Dim MyCount As Long
Dim MyPos As Long
Dim TestName As String
Dim WhatName As String
Dim LastFirst() As String
MyCount = MyList.Count
GETNAME = "not found"
For J = 1 To MyCount
TestName = UCase(MyList(J).Value)
WhatName = MyCell.Value
MyPos = InStr(WhatName, TestName)
If MyPos < 0 Then
LastFirst = Split(MyList(J), ", ")
GETNAME = LastFirst(1) & " " & LastFirst(0)
Exit For
End If
Next
End Function

Rick



"NunRacer" wrote in message
...
I've tried the first more simple formulas and yes yours works better
because
occasionaly i do have names with no preceding text. Also another problem
is
that some names have 2 first names which really throws things off. I
already
have master list of names about 400 or so that i do a vlookup and where
the
final result is the person first name with the last 3 letters of their
last
name eg. Joe Blo and their position in the company.
As of yet i havnt been able to get the macro to work, comes out as #name?

"Rick Rothstein (MVP - VB)" wrote:

I'm not trying to divert you from Bernard's solution, but given you said
you
were sort of new to Excel, I thought you might have missed the point
behind
the formulas Harlan and I have been writing back and forth about,
particularly the last one by each of us. Each of them can retrieve the
names
you are looking for without the need for a separately maintained "master
list" (that is, you do not need a Mylist like from Bernard's posting).
Just
put either of our last formulas in A1 and then copy them down as needed.
They will retrieve, and re-order, whatever names are contained inside the
text in column A, as long as those names are separated by a comma
followed
by a single space... and they will do it automatically, without needing a
separately maintained list to check against. By the way, the only
difference
between Harlan's and my last posted formulas has to do with where the
names
can occur within the text... my formula (a modification of Harlan's by
the
way) will allow the names to be at the beginning of the text whereas
Harlan's requires some non-name text to precede the names.

Rick


"NunRacer" wrote in message
...
I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called
Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started"
with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt
found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other
useless
info, and doesnt follow the same format) from a cell that matches
the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the
existing
names in the database.
The end result needs to be just their name.
Thank You.

Yeah that did the trick, thanks so much :)

"Bernard Liengme" wrote:

If you are getting #NAME! error you most likely have placed the function in
the wrong place
Use Tools | Macro | VBA editor
Make sure your workbook is select in the Project window (left hand panel)
Use Insert | Module
Past my function in the module window
Still not working? Send me a private message (remove TRUENORTH)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried the first more simple formulas and yes yours works better
because
occasionaly i do have names with no preceding text. Also another problem
is
that some names have 2 first names which really throws things off. I
already
have master list of names about 400 or so that i do a vlookup and where
the
final result is the person first name with the last 3 letters of their
last
name eg. Joe Blo and their position in the company.
As of yet i havnt been able to get the macro to work, comes out as #name?

"Rick Rothstein (MVP - VB)" wrote:

I'm not trying to divert you from Bernard's solution, but given you said
you
were sort of new to Excel, I thought you might have missed the point
behind
the formulas Harlan and I have been writing back and forth about,
particularly the last one by each of us. Each of them can retrieve the
names
you are looking for without the need for a separately maintained "master
list" (that is, you do not need a Mylist like from Bernard's posting).
Just
put either of our last formulas in A1 and then copy them down as needed.
They will retrieve, and re-order, whatever names are contained inside the
text in column A, as long as those names are separated by a comma
followed
by a single space... and they will do it automatically, without needing a
separately maintained list to check against. By the way, the only
difference
between Harlan's and my last posted formulas has to do with where the
names
can occur within the text... my formula (a modification of Harlan's by
the
way) will allow the names to be at the beginning of the text whereas
Harlan's requires some non-name text to precede the names.

Rick


"NunRacer" wrote in message
...
I think this is what i'm looking for now to make it work :)
I'm sort of new to excel.
Thanks everyone for the great responses.

"Bernard Liengme" wrote:

Somewhere in my workbook (it matter not where) I have a range called
Mylist.
I contains the names
Blow, Joe
Liengme, Bernard
Smith, Anne
Taylor, George

but, of course, it could be longer.

In A1 and A2 I have your data
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x


In B1 and B2 I call a UDF with =getname(A1,mylist)

The UDF is:

Function getname(mycell, mylist)
mycount = mylist.Count
getname = "not found"
For j = 1 To mycount
testname = UCase(mylist(j).Value)
whatname = mycell.Value
MyPos = InStr(whatname, testname)
If MyPos < 0 Then
getname = mylist(j)
Exit For
End If
Next
End Function

The formulas return the correct names from MyList

Need help with VBA? See David McRitchie's site on "getting started"
with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"NunRacer" wrote in message
...
I've tried searching for some solutions to my problem but havnt
found
anything that really helps me.

I want to extract a persons name (the name is mixed up with other
useless
info, and doesnt follow the same format) from a cell that matches
the
names i
have in a Database.

so example would be
3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x
3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x
So i need a formula to pull the name out by comparing it to the
existing
names in the database.
The end result needs to be just their name.
Thank You.