Hi All
I have source data that contains leading spaces in some blank cells. I've adapted the below to try and remove them using an array, but instead it is wiping column A. Could you help tweak it please?
Many thanks!
Please Login or Register to view this content.
Last edited by warp765; 04-16-2020 at 11:42 AM.
You need to remove this lineIt's resetting the array, which means it will then be blank.Please Login or Register to view this content.
Please Login or Register to view this content.
Many thanks @Fluff13 and BMV for your help, all sorted. Cheers
You're welcome & thanks for the feedback
Since you are using a fixed range, and if you do not mind using Excel's TRIM function (multiple adjacent internal spaces will be collapsed down to single spaces), then here is a one-liner that you can use...
Please Login or Register to view this content.
@Rick Rothstein Worksheet trim delete all multi spaces but VBA trim only Leading Spaces. However Evaluate could be used.
Last edited by BMV; 04-16-2020 at 03:34 PM.
@BMV,
All spaces? I did qualify my suggestion by including in parentheses this... "multiple adjacent internal spaces will be collapsed down to single spaces" which is what I think you meant when you said "all". I used the square bracket version of Evaluate because the OP appeared to indicate the range was fixed in size.
Last edited by Rick Rothstein; 04-16-2020 at 03:12 PM.
@Rick Rothstein sorry i miss "multi". and i mind trailing spaces will be deleted also.
Yes, I should have probably mentioned that one, but when I read this in the OP... "I have source data that contains leading spaces in some blank cells"... it made it sound like there were no trailing spaces to worry about. My assumption was that someone added leading spaces to an original text file to fill out a field of fixed length and it was those leading spaces the OP had to contend with when it was brought into Excel. I probably over-thought this one a little too much although it would have been helpful if the OP had included a sample file.Originally Posted by BMV
As to the original question for a left trim only, we can still do that without any loops (still a one-liner) and, again, assuming a fixed range of cells...
Please Login or Register to view this content.
Please Login or Register to view this content.
WOW! Thanks all, it was leading spaces only for me but great to see so many options!
Many thanks again.
@BMV,
I needed to add what is shown in red in order to make your code work correctly on my XL2010 worksheet...
Please Login or Register to view this content.
Last edited by Rick Rothstein; 04-16-2020 at 08:47 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks