Formatting cells

[Kazdima]

Hello everyone,
Would you please help me in formatting a cell?
I have a column A of 1500 rows. In each cell there is a Last and first names of different people. But there can be from 5 to 10 the same people with different formatting. For example, John Scott= one space between John and Scott, two, three spaces. Also, sometimes it is written as Scott James.
When I try to prepare report the same name is shown in 5 different rows instead of one, due to the problem in formatting.
Is it a way to format all cells in one manner?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thank you very much in advance for your help!
<o> </o>
Dan

 

[saltkev]

Hi

Not sure how to accomplish what you want via the Excel format, however this do what you need via a macro or Click Event. I am sure there will be someone who can give you your exact requirements.

Private Sub spacerbtn_Click()
 
Application.ScreenUpdating = False

For Each spacer In Range("a1:a1500")

If spacer > "" Then
        Cells.Replace What:="   ", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End If
Next spacer

Application.ScreenUpdating = True
 
 
End Sub

regards

 

[Kazdima]

Hello Saltkev,

Thank you for your help.

Yes, I hope that somebody else also will try to reselve my issue with Excel formatting.

Dan