+ Reply to Thread
Results 1 to 4 of 4

Delete text in middle of field

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Monroe, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Post Delete text in middle of field

    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.

  2. #2
    Registered User
    Join Date
    01-01-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Delete text in middle of field

    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.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Delete text in middle of field

    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
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    Monroe, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Delete text in middle of field

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1