I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

One note: My first instinct was Text to Columns, but not all the cells have a
Designer, so the PD would be in the wrong column half the time.

"berniean" wrote:

I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PD: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

Post a few samples of before/after

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"berniean" wrote in message
...
I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

Does this work for you:

=IF(ISNUMBER(SEARCH("designer",A1)),MID(A1,SEARCH( "designer",A1),
SEARCH("PD",A1)-(SEARCH("designer",A1)+2)),"")

?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"berniean" wrote in message
...
One note: My first instinct was Text to Columns, but not all the cells
have a
Designer, so the PD would be in the wrong column half the time.

"berniean" wrote:

I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PD: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is
extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

Hi,

Try this. This formula assumes that there will be a space after Designer:.
Also, it will extract all the text between Designer: and comma.

MID(B14,SEARCH(" ",B14,SEARCH("Designer:
",B14)+1)+1,SEARCH(",",B14,SEARCH("Designer: ",B14)+1)-SEARCH("
",B14,SEARCH("Designer: ",B14)+1)-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"berniean" wrote in message
...
I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

This works. Thank you very much.

"RagDyer" wrote:

Does this work for you:

=IF(ISNUMBER(SEARCH("designer",A1)),MID(A1,SEARCH( "designer",A1),
SEARCH("PD",A1)-(SEARCH("designer",A1)+2)),"")

?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"berniean" wrote in message
...
One note: My first instinct was Text to Columns, but not all the cells
have a
Designer, so the PD would be in the wrong column half the time.

"berniean" wrote:

I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PD: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is
extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

This works too, but I get a space before the name because there are double
spaces in some cells. I can fix that. Thank you.

"Ashish Mathur" wrote:

Hi,

Try this. This formula assumes that there will be a space after Designer:.
Also, it will extract all the text between Designer: and comma.

MID(B14,SEARCH(" ",B14,SEARCH("Designer:
",B14)+1)+1,SEARCH(",",B14,SEARCH("Designer: ",B14)+1)-SEARCH("
",B14,SEARCH("Designer: ",B14)+1)-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"berniean" wrote in message
...
I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

On Tue, 18 Aug 2009 15:50:01 -0700, berniean
wrote:

I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

If you want to use formulas, I would suggest the following. It should handle
any combination of PD, Designer and PM in any order; and will return a blank if
the substring doesn't exist. You could edit this to return something else.

They all use the same technique to find the beginning and end of the string.

I assumed that the comma was only used as a delimiter.

PM:
=IF(ISERR(MID(A1,FIND("PM:",A1),FIND(",",A1&",",
FIND("PM:",A1))-FIND("PM:",A1))),"",MID(A1,FIND(
"PM:",A1),FIND(",",A1&",",FIND("PM:",A1))-FIND("PM:",A1)))

Designer:
=IF(ISERR(MID(A1,FIND("Designer:",A1),FIND(",",A1& ",",
FIND("Designer:",A1))-FIND("Designer:",A1))),"",MID(A1,
FIND("Designer:",A1),FIND(",",A1&",",FIND("Designe r:",A1))-
FIND("Designer:",A1)))

PD:
=IF(ISERR(MID(A1,FIND("PD:",A1),FIND(",",A1&",",FI ND("PD:",A1))-FIND("PD:",A1))),"",
MID(A1,FIND("PD:",A1),FIND(",",A1&",",FIND("PD:",A 1))-FIND("PD:",A1)))

--ron

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"berniean" wrote in message
...
This works. Thank you very much.

"RagDyer" wrote:

Does this work for you:

=IF(ISNUMBER(SEARCH("designer",A1)),MID(A1,SEARCH( "designer",A1),
SEARCH("PD",A1)-(SEARCH("designer",A1)+2)),"")

?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"berniean" wrote in message
...
One note: My first instinct was Text to Columns, but not all the cells
have a
Designer, so the PD would be in the wrong column half the time.

"berniean" wrote:

I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PD: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is
extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"berniean" wrote in message
...
This works too, but I get a space before the name because there are double
spaces in some cells. I can fix that. Thank you.

"Ashish Mathur" wrote:

Hi,

Try this. This formula assumes that there will be a space after
Designer:.
Also, it will extract all the text between Designer: and comma.

MID(B14,SEARCH(" ",B14,SEARCH("Designer:
",B14)+1)+1,SEARCH(",",B14,SEARCH("Designer: ",B14)+1)-SEARCH("
",B14,SEARCH("Designer: ",B14)+1)-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"berniean" wrote in message
...
I have a number of cells in a spreadsheet that contain the following:
PM: John Smith, Designer: Joe Blow, PM: Jane Doe

I can get the PM using =LEFT(A1,FIND(",",A1,1)-1)
I can get the PD using RIGHT(A1,LEN(A1)-FIND("PD:",A1,1)+1)

My question is, how do I get the Designer? I've tried combinations of
MID/FIND but I keep getting the #VALUE# error.

I need to separate these into their own columns and the list is
extensive,
else I'd retype it. The 3 names change from row to row.

Thanks,
Bernie