How do I strip punctuation from end of text?

Hi All. I have a macro that should strip the punctuation from the end of text in a range. When I run the macro it eliminates the text as well as the punctuation. Can someone please look at this code and tell me what I;m doing wrong? Thank you so much. I am including a spreadsheet with sample data. The code is below.

Please Login or Register  to view this content.

A question mark replaces any text. To replace an actual question mark, precede it with a tilde, viz: "~?". Even that is not going to remove punctuation from the end of your text though, but from anywhere in the text.

I have attached your test workbook with some modifications to Sub StripPunctuation that will remove punctuation only from the end of your text as you had asked. The code you have written will remove punctuation from anywhere in the text (which may be your intention but not what you asked).

There are two methods. Obviously, you only need to code one of them. Method 1 removes anything that is not alphanumeric, so it will get all punctuation. Method 2 removes only the specific punctuation that you set. That also answers your question about whether it will work on other punctuation marks as well.

Sorry for my shorthand. I've uploaded the same code using longer variable names.

Basically: s (now sValue) is a string to hold the value of the cell for processing; i (now iIndex) is an index into the value string to extract characters one by one from the right hand side; and c (now sChar) is the character that has been extracted from sValue according to the value of iIndex. VBA does not have a single Character type so we are forced to use a String type and limit it to one character at a time.

The constant serves two purposes. Firstly, if it is set to an empty string then the code does not search for specific punctuation marks but assumes that anything which is neither alphabetic nor numeric will need to be removed. Note that it takes the upper case of the character so that if it is alphabetic then it only needs to check the range "A" to "Z"; it will never be "a" to "z".

The second purpose of the constant is to hold particular punctuation marks if those characters alone need to be removed. It takes one character at a time from the right hand side of the value and checks that character's position within the constant. If a value greater than zero is returned then it knows the character is among those which are disallowed. However, if a zero is returned then the character was not found in the constant and therefore must be allowed; hence the strip back has ended.

When it exits the For loop, whatever index it has counted down to is the length of the allowed string, so it takes everything up to that point as the new value.

Anyhow, take a look at the new upload. I hope it is clearer now.

Based on the example you posted:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

It's impossible that all text will be removed when using:

Please Login or Register  to view this content.

Some code is easier to adapt to other uses than other code.

This is an Excelforum.
Please post Excel-files.

Originally Posted by snb

It's impossible that all text will be removed when using:

Please Login or Register  to view this content.

Some code is easier to adapt to other uses than other code.

SNB, the reason this code will remove all text is that a question mark matches any character when using Replace. In order to replace an actual question mark, you need to specify "~?". The tilde (~) acts as an escape character and tells the parser to interpret the next character exactly as is.

Thanks for pointing that out to me

resulting in

Please Login or Register  to view this content.

But if only the last puctuation should be removed I'd prefer the oneliner :

Please Login or Register  to view this content.