Extract text from cell

[berniean]

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

 

[berniean]

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.

 

[Don Guillett]

Post a few samples of before/after

 

[RagDyer]

Does this work for you:

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

?

 

[Ashish Mathur]

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]

This works. Thank you very much.

Does this work for you:

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

?

 

[berniean]

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.

 

[Ron Rosenfeld]

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("Designer:",A1))-
FIND("Designer:",A1)))

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

--ron

 

[RagDyeR]

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

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com