Excel VBA advice needed.

[LaraC]

I have a text field where people can type anything, however they frequently type a leading space before punctuation marks. To correct it I’ve compiled a macro to remove the leading space.

Is there another method to accomplish the task?
Can the sub be converted to a function?
Is there a more elegant way of coding instead of listing the replacement arguments on a separate line?

As an example the code will remove the spaces before the comma and the full stop in the following sentence:
"All bad dogs bark all night long , how annoying ."

Ps. I’m using Excel for Windows.

Sub Replace_spaces()
    
    Columns("A:A").Select
    Selection.Replace What:=" ,", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" .", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

 

[froot]

There is a more eloquent way of doing it.

You don't really want to go around selecting cells the whole time - it makes things a lot slower.

dim value as varchar
value = Cells("A1").Value2
value = Replace (value, " ,", ",")
Cells("A1").Value = value

 

[JustinB]

Does it have to be in VBA? you could use one of excel's built-in functions to do the same. and nest them?

 

[LaraC]

Thank you JustinB.

The idea is to replace all the errors in the column.
If I use Excel functions I’ll have to create another column for the text that were corrected using the Substitute function.
The formula will also grow very lengthy if you want to change multiple errors.

 

[LaraC]

@froot How will you accomplish the task if you need to replace more that one character?

 

[Beachless]

There is a more eloquent way of doing it.

You don't really want to go around selecting cells the whole time - it makes things a lot slower.

dim value as varchar
value = Cells("A1").Value2
value = Replace (value, " ,", ",")
Cells("A1").Value = value

Creating a new variable for each cell is going to be pretty harsh.

OP Yours only takes 0.1 seconds to execute on 5000 small sentences on my laptop so I dont think you have to worry about it.

 

[patrick123]

There is a more eloquent way of doing it.

You don't really want to go around selecting cells the whole time - it makes things a lot slower.

dim value as varchar
value = Cells("A1").Value2
value = Replace (value, " ,", ",")
Cells("A1").Value = value

@froot How will you accomplish the task if you need to replace more that one character?

Regular expressions might be your answer. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops