title: In Excel 2010, how to delete a portion of a string within a cell using a Macro?
body: <p>I am trying to create a macro for an Excel file which has a column (let's call this column A) of cells, and each cell contains 4 to 5 project Deliverables &amp; Work Products separated by a comma, as well as identified by a tag "DEL" for Deliverable and "WKP" for Work Products. </p>
  
  <p>I wish to accomplish the following with a Macro:</p>
  
  <ol>
  <li>I would like to keep column A as it is.</li>
  <li>Create a column B for Deliverables, which need to be extracted from cells in Col A </li>
  <li>Likewise, create a column C for Work Products, which need to be extracted from cells in Col A </li>
  </ol>
  
  <p>Let me give an example:</p>
  
  <p>Supposing Cell A1 contains</p>
  
  <pre>DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule, WKP1: WKP 1 Work Product Document</pre>
  
  <p>Here's what I want Cell B1 to look like:</p>
  
  <pre>DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule</pre>
  
  <p>And Cell C1 to look like this:</p>
  
  <pre>WKP1: WKP1 Work Product Document</pre>
  
  <p>And mind you, not all the cells in Col A will be the exact character length as Cell A1. But they will contain both DELs and WKPs. </p>
  
  <p>Given that the names of the Deliverables and Work Products vary in length, I cannot simply copy Col A and run TextToColumn in order to split it. </p>
  
  <p>So far, the approach I have come up with is to copy Col A to Col B, retain the Deliverables and try to remove Work Products. </p>
  
  <p>As you can see, this entails deleting the part of the string that starts with the comma (",") and space (" ") in front of "WKP" and ends with the last character of the Work Product in question (which in this case would be the last character of the entire string itself since it seems that the WKPs are mentioned right after DELs). </p>
  
  <p>While I am able to write a Macro the can find the text "WKP" within each cell range and delete it, I am not able to delete the stuff that comes right after it. In other words, I am only able to produce this for Cell B1: </p>
  
  <pre>DEL1: DEL 1 Project Process, DEL2: DEL 2 Project Schedule, 1: 1 Work Product Document</pre>
  
  <p>Can someone help me write the Macro to do this? I would appreciate it if the syntax was also explained. </p>
  
  <p>Thanks!</p>