Text clean up 1

[clifftech]

I need to develop a macro that will look at the text string value for each row (about 50 rows) in column A and trim 1 or more spaces from the end of the text string. Spaces in the middle of the text string are OK. The spaces at the end of the string need to be eliminated so that the strings can be matched in other functions I am using.

Thanks for the help.

 

[clifftech]

Sorry for the duplicate thread. Having problems with our Internet server.

 

[mintjulep]

Since you talk about rows and columns I will assume this is Excel.

What is wrong with the native Trim() function?

 

[clifftech]

The trim function would eliminate spaces in the middle of the text as well as at the end but I only need the spaces at the end of the text string. I need this to be performed as a macro, not as a function.

 

[mintjulep]

You said it was "ok" to leave multi spaces in the middle, not that you had to leave them.

In VBA there is rTrim.

 

[SkipVought]

"The trim function would eliminate spaces in the middle of the text ..."

NOT SO!!!

TRIM removes LEADING and TRAILING SPACES ONLY.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mintjulep]

Not quite Skip,

Trim() as a worksheet function removes multi-spaces internal to a string, leaving a single space.

From Excel help:

TRIM

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

 

[SkipVought]

I did not realize that the question related to MULTIPLE spaces between words..

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[fumei]

Confusion between Trim as a worksheet function, and Trim as a VBA function.

The VBA function Trim does NOT remove multiple internal spaces. It removes leading and trailing spaces ONLY.

The worksheet function Trim does remove multiple internal spaces.

As the OP wrote: "I need to develop a macro that will look at the text string value", (my emphasis) then one would assume that VBA is being used. Therefore, it is not unreasonable to think in terms of VBA functions.

Of course, this may not be totally correct. The OP also wrote:

"The spaces at the end of the string need to be eliminated so that the strings can be matched in other functions I am using"

This can mean a combination of worksheet functions, in which case, the worksheet function Trim may be viable.

faq219-2884

Gerry
My paintings and sculpture