macro to trim only text in mixed format spreadsheet

I have spreadsheets that contain columns of text, numbers, function of those numbers, etc. I would like to develop a macro that will search the spreadsheet for any cells that contain text and trim the string within. I've found macros already written that trim all cells in a spreadsheet, but they change cells using formulas to value only. I need to keep the formulas active. Here is a macro I found in a forum that trims a column of text but you have to define the column and range each time:

Please Login or Register  to view this content.

I don't have enough vba knowledge yet to simply make the macro apply to any one text column by just highlighting a range, OR enough know-how to tell the macro to go from one column to the next to search for text to trim. Please let me know how to edit it to achieve my goals. Thank you!

attach a sample file with currend tabḷe and desired result

Thank you for reponding.
I have attached a test file in which I'd like to trim the text in columns H & J in one macro without affecting the format of the rest of the spreadsheet. The ("manual") macro I've entered has not been applied to those columns yet.

where is the desired result ?

Hi, patel45. The desired result is columns H & J without spaces at the beginning or end. I can do it individually on each column but want a macro for both columns or any other text columns I add to the spreadsheet. The attachment I sent earlier doesn't have any macros applied. Visually, the original file and desired result will look the same because you can't see spaces (present or deleted) at the end of text.

@burnettec,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

OK, thank you. I actually read that after the fact. My apologies. Would you like me to "solve" the post and restart it fresh. The macro code is actually in the attachment later on in the replies.

No you can just go to post #1 and add the tags.

Done! Thanks for guidance.

Is the best way to approach my issue to edit the current macro to incorporate a loop that searches for columns of text, trims them, goes to the next column, etc. Can someone get me started on how to write that? It's an if/then/else-thing?

Originally Posted by burnettec

The desired result is columns H & J without spaces at the beginning or end.

Am I correct in seeing that these two columns only have text and these are the two columns you want to trim?

yes. those are the only text columns for now... i will add more later as i get more data and will want to trim all the columns in the spreadsheet for reasons I won't detail other than i may need to compare sells to ensure matching text.

Here is a macro written by AlphaFrog which I modified slightly.

With this macro it will allow you to click on the first cell in the column you want to trim and will select the last cell in that column.

Please Login or Register  to view this content.

Thank you for your suggestion but I got a runtime error 9, subscript out of range. I assumed the message box implied i could click on any cell i wanted to begin with? Not just the first cell of the column?

Did you update the sheet name to the proper name?

Please Login or Register  to view this content.

No. Duh. I did make sure i was on the first sheet but forgot quotes mean text. Sorry. Just finished an Excel VBA class. Will test again.

That worked, thanks. Thanks to alphafrog, whoever you are. This is not exactly my end goal for a macro, but it is much better than having to enter where to start cleanup and I can work with it as far as looping across the spreadsheet. For that matter it may be better to retain some control about where to start cleanup instead of letting loose with a super-macro until I better understand vba. Don't want to mess up data for the sake of proving i can write (or can't write) code. --- will consider this thread "solved" (when I figure out how to edit that status...)

Glad it works out for you and happy to have helped.