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

Try this:

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

Hope this helps.

Pete

Hi,

Thanks a million, it work a dream.

Why minus 1 and minus 2?

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