• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

White space

bobhc

bobhc

Excel Ninja
Hi all, old man again with the need of more help.

I have a two columns of cells with full names and address, the problem is that some people have the habit of hitting the space bar before and/or after entering data.

I have to remove this white space before and after the data BUT NOT the white space in-between the names, so _john_smith_ would become john_smith ( _= white space)I have looked at =substitute(A2," ","") and the suggestion of Hui's (did a search) of trim but these are for single cell use. How can I enter a formula and select all the required cells,including cells without white space, as not all who input to a shared workbook enter data the same way or am I asking for some thing that does no exists.
 
Assuming data is in cells A2:B100.


In C2:

=TRIM(A2)


Copy cell, paste to range C2:D100.

Copy this data, then select A2 and Paste Special - Values only.
 
Hi, bobhc!


Additionally to what Luke M posted, if you can't or don't want to use helper or auxiliary columns, you can do this:


a) select the range to be unwhitespaced and name it "RangeToBeUnwhitespaced"


b) copy & paste the following code into a new or existing module (Alt-F11, tab Insert, Module)

-----

[pre]
Code: 
Option Explicit

Sub Unwhitespace()
' constants
Const ksRange = "RangeToBeUnwhitespaced"
' declaration
Dim rng As Range, I As Long, J As Integer, A As String
' start
Set rng = Range(ksRange)
' process
With rng
For I = 1 To .Rows.Count
If Len(.Cells(I, 1).Value) = 0 Then Exit For
For J = 1 To .Columns.Count
A = Trim$(.Cells(I, J).Value)
If Len(A) > 0 Then
Do Until InStr(A, Space$(2)) = 0
A = Replace(A, Space$(2), Space$(1))
Loop
.Cells(I, J).Value = A
End If
Next J
Next I
End With
' end
End Sub
[/pre]
-----


c) run the macro (Alt-F8, Unwhitespace)


d) check the line "If Len(.Cells(I, 1).Value) = 0 Then Exit For" to verify it's suitable for your data, it exits when cell in column A of a row is empty; otherwise, comment it with a '


Just advise if further assistance needed.


Regards!


PS: doing d) before c) is highly recommended...
 
Good day to the both of you

I have tried both answers (it's seven at night and my wife thinks I need to see a doctor,bless her)and both work. The answer from Luke M seems to be the quicker to use and the auxiliary columns are not a real problem as they can be deleted when the formula has done it's work, but the answer from SirJB7 is,to me, the better of the two when used for many,many rows/columns/data.

Question.....I have not used VB much.If SirJB7's code available to all work books from now on????
 
Hi, bobhc! (aka old man new-tricked and wife controlled)


Yes and no.


Yes:


a) Manually: if you insert that code in any module of any workbook (not worksheet, just once per Excel file) and you define a named range called "RangeToBeUnwhitespaced" in it (that can be different in for each workbook).


b) Automatically: if you move that code to a personal macro book named "PERSONAL.XLSB" and store it in "C:Users < username > AppDataRoamingMicrosoftExcelXLSTART" (this is for Windows 7 and Excel 2010, if you have another OS or Excel versions let me know)

If you choose this option, I'd suggest a little modification in the code, so as to avoid redefining the standard range "RangeToBeUnwhitespaced" in different sheets in same workbook or in different workbooks.

You should replace this line:

-----

Code: 
Set rng = Range(ksRange)

-----

by these lines:

-----

[pre]If Selection.Cells.Count = 1 Then
Set rng = Range(ksRange)
Else
Set rng = Selection.Cells
End If
[/pre]
-----


It'll work then in two ways:

1) defining a specific named range in active workbook with the standard name range, and selecting one cell

2) not need to define any range, and it'll run on selected cells

That's to say: if you want a range, use the range, and prior running the macro be sure that only one cell is selected; if you want to use a selection of cells, just select them and run the macro.


No: if you don't do any of that. :)


Just advise if any trouble.


Regards!


PS: please note that the difference between both methods is not only the use of auxiliary columns, but the elimination of more than one intra-string spacing (" aaa (20 spaces) bbb " will remain "aaa (20 spaces) bbb" with trim$ function and "aaa bbb" with Unwhitespace macro).
 
bobhc,


The macro would only be available to that particular workbook. You'd need to put the macro in some sort of personal add-in or the Personal.xls workbook for it to be available everywhere.


Actually, for a bit of a correction, the macro might actually not work as well for more data. The macro uses several loops, so it's analyzing each cell one at a time (albeit, very quickly). If more columns are analyzed, that's more cells it has to work through. On the other hand, the formula solution is very fast, and is only limited by space (although you could always use a new workbook/worksheet).


SirJb7,

PS: please note that the difference between both methods is not only the use of auxiliary columns, but the elimination of more than one intra-string spacing (" aaa (20 spaces) bbb " will remain "aaa (20 spaces) bbb" with trim$ function and "aaa bbb" with Unwhitespace macro).
Click to expand...

That's not correct, TRIM will remove the extra spaces between text as well.
 
Hi, bobhc!

Luke M is right, trim$ function will do the same job. I always supposed it just worked for leading and trailing spaces... Every day I learn something new!

Regards!


@Luke M

Hi!

Absolutely right your observation about trim$ function. Thanks.

But I don't agree with problems arising with extended data ranges. It just operates when needed, not when eventually not needed recalculations.

Regards!
 
@Luke M

Hi!

I don't know if you knew it yet, I didn't, so just in case:

I tried the Trim$ VBA function because I said "if they're both similar, why don't use it and eliminate the loop?"... And they're not similar, the VBA function doesn't remove the intra-string redundant spacing. I suppose I got confused because of that... I simply didn't know they work differently.

Regards!
 
You must log in or register to reply here.
Back
Top