Stripping The Rubbish

**Programmin'Pete** · 11-03-2009, 07:45 PM

I have a spreadsheet with several hundred rows, and part of each of is 5 cells with description of the coupon cost of freight cahrges, in the following format

xx M & xx E

The xx before the M and the E can be 1 or 2 digits, and I want to make all the cells just the value between the & and the E as the rest is redundant how the form is setup. I have tried reading webs sites to do this, and cant figure out how to use the find/replace excel tool. Is there an easy way i can do this and highlight all the cells to do it in one go????
thanks

**JBeaucaire** · 11-03-2009, 07:59 PM

Let's say that string value is in column C.

1) Off the the right in an empty column, enter this formula:

=TRIM(MID(C1,FIND("&",C1)+2,2))+0

Adjust that red value to the correct reference for the first value.

2) Copy that down the whole data set, the new values will appear

3) Copy the whole column of new values

4) Click on the old column and select Edit > Paste Special > Values

5) The new values are now in the old column, you can delete the new column

**Programmin'Pete** · 11-03-2009, 08:14 PM

That code works and gives me the right values I wanted to keep, but how would u modify that so if you delete the original columns so that the cells that code created are the ones being used now?

**JBeaucaire** · 11-03-2009, 08:27 PM

Steps 2, 3 and 4 above indicate how to make the new values the permanent ones. Did you go through all the steps?

If you don't want to follow those instructions and replace the old values with the new ones, you can use the same steps to copy/pastespecial>values the new data over the top of itself to remove the formulas.