+ Reply to Thread
Results 1 to 4 of 4

replace/remove space and character

  1. #1
    Registered User
    Join Date
    09-27-2018
    Location
    london
    MS-Off Ver
    excel
    Posts
    69

    replace/remove space and character

    Hi guys,

    Wandering if anyone can help

    Is there a formula that can do the following:

    starting looking at the any given cell from right and when first space is found remove the space and the previous letter ( the first line below would be space and letter U)

    note the scenarios below, sometimes there is no space at all or there is a space but at the beginning of the cell. I am basically interested in removing/replacing the last space and preceding letter of at given cells.

    I18S19/0131 U
    I18S19/0065 S
    I18S19/0112
    STS PYT-Camberle

    Any help would be great

    Thanks in advance

    Vito

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,734

    Re: replace/remove space and character

    Try this:

    =IF(MID(A1,LEN(A1)-1,1)=" ",LEFT(A1,LEN(A1)-2),A1)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-27-2018
    Location
    london
    MS-Off Ver
    excel
    Posts
    69

    Re: replace/remove space and character

    Hi,

    Thanks a million, it work a dream.

    Why minus 1 and minus 2?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,734

    Re: replace/remove space and character

    LEN will give the number of characters in A1, and you want to test if the character just before the last one is a space, hence -1.

    If it is a space, then you want to take the left hand characters minus the last 2 (i.e. including the space), hence -2.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum (in terms of post-count), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove unknown character and extra space from Name
    By senthile in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-20-2016, 04:06 AM
  2. How to remove space after a particular character
    By sugaprasad in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-20-2015, 05:02 AM
  3. Replies: 0
    Last Post: 08-19-2014, 11:02 AM
  4. Remove Space from first character of text
    By jaymkent in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2012, 07:19 AM
  5. How do I replace a BEL Character of 7 with a space
    By KBear in forum Excel General
    Replies: 1
    Last Post: 06-06-2006, 11:00 AM
  6. How do I replace a BEL Character of 7 with a space
    By KBear in forum Excel General
    Replies: 0
    Last Post: 06-06-2006, 10:50 AM
  7. Replies: 3
    Last Post: 02-27-2006, 12:10 AM

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