从Excel单元格中删除开头和结尾的空格 [英] Remove Leading and Trailing Whitespace from Excel Cells

问题描述

我有一个Excel电子表格，其中包含多个单元格，这些单元格具有前导或尾随空格(包括空格，不间断空格，回车符和换行符).我要删除这些空白.我的Google搜索表明，这只能通过VBA来完成.VBA Trim 函数不会删除所有类型的空格，也可能会从单元格的中间删除.VBA的Trim函数的MSDN页面引用.NET的字符串.Trim 方法完全可以满足我的需求.但是，我不确定如何按照第一个链接的建议从VBA调用String.Trim.如何从Excel VBA调用String.Trim方法?

I have an Excel spreadsheet with several cells that have leading or trailing whitespace (including spaces, non-breaking spaces, carriage returns, and new lines). I want to remove these white spaces. My Google searching suggests this can only be done with VBA. The VBA Trim function doesn't remove all types of whitespaces and may remove from the middle of the cell as well. The MSDN page for VBA's Trim function references .NET's String.Trim method which does exactly what I want. However, I'm not sure how to call String.Trim from VBA as suggested by the first link. How do I call the String.Trim method from Excel VBA?

为澄清起见，我想在单元格的中间保留空格(包括回车和换行).我只想删除单元格开头和结尾的字符.

To clarify, I want to keep the whitespace (including carriage returns and new lines) in the middle of the cell. I only want to remove the characters from the beginning and the end of the cell.

推荐答案

我希望您的问题现在已经解决.我仍然会把答案留给那些可能仍需要的人.我也是这样做的，只有经过广泛的研究后才发现这一点，因为大多数答案都是循环使用，而我通常不惜一切代价避免这样做.至少是显式循环！

I hope your issue is resolved by now. I'm still going to put my answer out there for those who might still need it. I did too, and only came across this after extensive research, since most of the answers used loop which I generally want to avoid at all cost; at least the the explicit looping!

因此，对我来说，这些简单的代码行就像是一种魅力:

So, for me, these simple lines of code worked like a charm:

Dim Rng As Range
Set Rng = Range("A1").CurrentRegion 'Select the Range based on your criteria, preferably smaller
Rng.Value = Application.Trim(Rng)

希望它会有所帮助:)

这篇关于从Excel单元格中删除开头和结尾的空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！