Delete text in middle of field

**Beckl46** · 01-24-2013, 10:45 PM

Hi. I have a field in Excel 2007 that I need to delete text in the middle. Each field has "," (quote comma quote) in it and I need to delete the character that is 4 spaces to the left of it and 4 spaces to the right of it. Is there a way to do it, almost like the LEFT and RIGHT commands remove from the front and end of a field? Somehow where it can locate that text (",") and work right and left from it? It is NOT a uniform number of spaces in each field to reach the text (",") (like always 28 characters from the beginning). Thanks.

**ItPaysTheBills** · 01-24-2013, 10:52 PM

Don't have time to work at this at the moment, but consider using the Find, Mid, and Len functions as well to work towards your solution. Some combination of find, mid, len, left, and right is going to solve the issue for you. I've done something similar to this in a long lost work spreadsheet.

**Special-K** · 01-24-2013, 11:12 PM

Try this

=LEFT(A1,FIND(CHAR(34)&","&CHAR(34),A1)-5)&MID(A1,FIND(CHAR(34)&","&CHAR(34),A1)-3,9)&RIGHT(A1,LEN(A1)-FIND(CHAR(34)&","&CHAR(34),A1)-6)

Given
abcde","fghijkl
this produces
acde","fghjkl

The characters b and i are removed, ie 4 to the left of "," and 4 to the right

**Beckl46** · 01-26-2013, 09:32 AM

Special-K. You are amazing. That worked perfectly. Thank you so much for your help. It looks like you used CHAR(34) for the quote symbol because the formula needs to use " to surround the comma. Am I understanding that right?