EXCEL: How can I remove spaces?

[mrfritz44]

I would like to start with a blank spreadsheet that uses some technique (macro, function?) to automatically remove spaces from a cell when text is pasted into it. Better yet, I would want to remove instances of double-spaces with nothing. The goal is to automate the process of forming a straight block of text from text that contains a lot of extraneous spaces.

I have this function, but I don't know how to use it:

Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
End Function


Any ideas?

Thanks,

Fred

 

[KenWright]

Personally i just have a macro that I use to do just that. You select the text in question and then just run it - Is a lot more flexible that way, and certainly no need to use a UDF which involves other columns etc.

The routine I would recommend is one by a guy called Dave McRitchie (MS Excel MVP), and I just wouldn't be without it now.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
                      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next   'in case no text cells in selection
    For Each cell In Intersect(Selection, _
                               Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mrfritz44]

Thanks very much for the tip. From a newbie perspective, how do I get this code into Excel to actually use as you described?

Thank you!

Fred

 

[mrfritz44]

I actually got it to run, but it won't work with the block of text I'm trying to clean. I'll post the text block here and maybe you can deduce what modifications needs to be made to the macro?

Thanks,

Fred

Here's the text block:

Note that the following scripts will take a long time to run as they read the entire Anc file!

Script to identify ANC records with error #08 "Ev-Subject Error, null not allowed"
select where EV-PTR = 0 OR EV-PTR = 8224;
l d @prikey, ev-use, ev-ptr, ev-date;go;
Script to identify ANC records with error #68 "SERVCE Constr 18: END-DTTM must be Null or >="
SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)
AND ANC-EXAM-STOP-DATE = 12336;
l d @prikey, ev-date, anc-exam-stop-date;go;


FIX SCRIPT:
Script to fix ANC records with error #68. We will set the ANC-EXAM-STOP-DATE to 0 for these records. The reason we set these to 0 is because the value currently in those records is a binary representation of two zero characters. There was a bug in the LW application that moved zeroes to the ANC-EXAM-STOP-DATE-G. This is a character field so the application moved two zero characters "00" to that field. Those zero characters have a binary representation of 12336. 12336 is 3030 in hexadecimal. The character "0" has a hexadecimal value of 30. So, what the application was attempting to do was move zero to the ANC-EXAM-STOP-DATE field and the following Entrude will do the same.
SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)
AND ANC-EXAM-STOP-DATE = 12336;
l begin c anc-exam-stop-date = 0;update;end;go;

 

[KenWright]

Apologies but I may be slightly confused as to what you want here.

Are you saying you want to remove ALL spaces from the text such that you end up with a long string of characters

or

are you saying you want to clean up the data and replace double spaces with single spaces etc, such that you have a legible piece of text?

It's just that what you posetd already looks reasonably clean to me, so i may have misunderstood what you are looking for.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mrfritz44]

Sorry for being vague. The goal is to make the above text block appear as I've pasted below:

Note that the following scripts will take a long time to run as they read the entire Anc file! Script to identify ANC records with error #08 "Ev-Subject Error, null not allowed" select where EV-PTR = 0 OR EV-PTR = 8224; l d @prikey, ev-use, ev-ptr, ev-date;go; Script to identify ANC records with error #68 "SERVCE Constr 18: END-DTTM must be Null or >=" SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)AND ANC-EXAM-STOP-DATE = 12336; l d @prikey, ev-date, anc-exam-stop-date;go; FIX SCRIPT:Script to fix ANC records with error #68. We will set the ANC-EXAM-STOP-DATE to 0 for these records. The reason we set these to 0 is because the value currently in those records is a binary representation of two zero characters. There was a bug in the LW application that moved zeroes to the ANC-EXAM-STOP-DATE-G. This is a character field so the application moved two zero characters "00" to that field. Those zero characters have a binary representation of 12336. 12336 is 3030 in hexadecimal. The character "0" has a hexadecimal value of 30. So, what the application was attempting to do was move zero to the ANC-EXAM-STOP-DATE field and the following Entrude will do the same. SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)AND ANC-EXAM-STOP-DATE = 12336; l begin c anc-exam-stop-date = 0;update;end;go;

 

[SkipVought]

Please be clear.

You do not want to remove SPACES.

You want to remove paragraph marks or line feeds or carriage returns.

TRUE?

Is this text in separate ROWS or is it all in one cell, in a single string?

Skip,
[sub]
[red][/red]
[/sub]

 

[mrfritz44]

I want to remove everything necessary to make the text appear like the block I manually edited above. This text is all in one cell.

I'm not sure what characters are keeping the lines from formatting. I tried chr(9), chr(10), & chr(12).

Any ideas?

Fred

 

[SkipVought]

Sub RemoveParagraphs()
    Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[mrfritz44]

Thank you for the attempt, but it did not work.

Try pasting the text I listed above on your funciton. It appears to do nothing.

Thanks,

Fred

 

[SkipVought]

EDIT the cell and then tell me.

Skip,
[sub]
[red][/red]
[/sub]

 

[mrfritz44]

I highlight the cell and then run the macro. IS this what you mean? Similar funcitons work for text typed into Excel, but not the copy and pasted block.

Fred

 

[SkipVought]

select the cell, F2 (EDIT) and ENTER

Skip,
[sub]
[red][/red]
[/sub]

 

[Gavona]

I'm not sure what characters are keeping the lines from formatting. I tried chr(9), chr(10), & chr(12).
Any ideas?

Personally I think the first thing to do is to find out what those characters are. Forget automating the process for the moment.

Paste your text into A2
In cell B1 put the number 1, in cell B2 the number "2" etc.

In cell B2 put the formula:
=CODE(MID($A2,B$1,1))
In cell B3 put the formula
=char(B2)
The first formula will tell you the code for the nth character in cell A2, where n is the value in B1.
The second formula displays the character represented by the code. This just gives you a simple visual check - you will only be interested in cells that appear to be blank, or display strange character.
you can drag those formulae across several columns to 'analyse' several chunks of text at a time.

Actually I would modify the above approach so that in A2 I would put not the entire text but bits with those offending characters that you have cut out of the full text (edit the cell, select around the offending characters, ctrl-c to copy.)

If you want to use code to help then try this. Select the cell containing your text and then run the macro. Text to the left of the character being tested is one colour, text to the right is another. The character being tested is red (assuming it is visible). For non-standard characters a message box is displayed identifying the character code.
The Select Case part excludes ranges of acceptable character codes so only unusual ones are shown in the messagebox. Play with these until only invalid characters are displayed.

Sub Macro1()
Dim MyCell As Range
Set MyCell = Selection.Cells(1, 1)
For i = 2 To Len(MyCell)
    With MyCell
        .Characters(Start:=1, Length:=i - 1).Font.ColorIndex = xlAutomatic
        .Characters(Start:=i, Length:=1).Font.ColorIndex = 3
        .Characters(Start:=i + 1, Length:=Len(MyCell)).Font.ColorIndex = 4
        MyCode = Asc(Mid(MyCell, i, 1))
        Select Case MyCode
            Case 43 To 90
            Case 97 To 122
            Case Else
                Response = MsgBox("Character Code of red character is" + vbLf + _
                Str(MyCode), vbOKOnly)
        End Select
    End With
Next i
MyCell.Font.ColorIndex = xlAutomatic
End Sub

Gavin

 

[mrfritz44]

This is the block of text:

Note that the following scripts will take a long time to run as they read the entire Anc file!

Script to identify ANC records with error #08 "Ev-Subject Error, null not allowed"
select where EV-PTR = 0 OR EV-PTR = 8224;
l d @prikey, ev-use, ev-ptr, ev-date;go;
Script to identify ANC records with error #68 "SERVCE Constr 18: END-DTTM must be Null or >="
SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)
AND ANC-EXAM-STOP-DATE = 12336;
l d @prikey, ev-date, anc-exam-stop-date;go;


FIX SCRIPT:
Script to fix ANC records with error #68. We will set the ANC-EXAM-STOP-DATE to 0 for these records. The reason we set these to 0 is because the value currently in those records is a binary representation of two zero characters. There was a bug in the LW application that moved zeroes to the ANC-EXAM-STOP-DATE-G. This is a character field so the application moved two zero characters "00" to that field. Those zero characters have a binary representation of 12336. 12336 is 3030 in hexadecimal. The character "0" has a hexadecimal value of 30. So, what the application was attempting to do was move zero to the ANC-EXAM-STOP-DATE field and the following Entrude will do the same.
SELECT WHERE (ANC-EXAM-STOP-DATE > 0) AND (ANC-EXAM-STOP-DATE < EV-DATE)
AND ANC-EXAM-STOP-DATE = 12336;
l begin c anc-exam-stop-date = 0;update;end;go;





WHAT IS THE OFFENDING CHARACTER?

 

[mrfritz44]

It's a chr(10), which I already tried in the previous replacement macro...........ahhhhhhhh!!!!!!!!!!!

 

[mrfritz44]

Using this macro with no luck:

Sub RemoveParagraphs()
Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[mrfritz44]

Update....that funciton works but there seems to be a character limit on the cell for the macro to work. I trimmed down the text block a bit and then it works. There seems to be a limit around 600 charcters that makes it not work. This is too bad since it seems like a very useful macro if I could apply it to a whole sheet with large cells.

Thanks all for your help and if you know any work arounds for the character limit please let me know.

Thanks,

Fred

 

[Gavona]

Sub Macro1()
Dim MyCells As Range

For Each MyCell In Selection
    MyCell.Value = Replace(MyCell.Value, Chr(10), " ")
Next MyCell
End Sub

Gavin

 

[Gavona]

Equally the formula
=Clean(a1)
appears to work (and replaces double spaces) but either way word wrapping within the cell seems to fail after 1134 characters.


Gavin