| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract text from cell
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract text from cell
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract text within two specific values within a cell | Excel Worksheet Functions | |||
| Find and extract text from cell | Excel Discussion (Misc queries) | |||
| Extract a specific portion of text as new cell value | Excel Discussion (Misc queries) | |||
| HOW TO EXTRACT CELL REFERENCE AS TEXT FROM NAMED RANGE | Excel Worksheet Functions | |||
| formula to extract partial content (text) of cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
