Replacing double spaces by a single space, removing all commas & deleting full stops after the last words in cells

[CasperA]

I would like to do the following, preferably in one step, otherwise in different steps:

a) Replace all double spaces in all cells in columns B and H until all words in the cells are separated by one space only
b) Remove all commas from all cells in columns B and H
c) Remove all full stops after the last words in all cells cells in columns B and H, but not in other positions in the cells

Would there be a way to do this in Excel?

Thanks,
CA

 

[deletedalien]

To do a)

Select columns B:H > open find and replace (ctrl + H) then under find what type (2 spaces) under replace with type (1 space) click replace all

To do b) Same as above

To do c) u might need a helper cell and do =LEFT(A1,LEN(A1)-1) (if data starts in A1)

Otherwise you would need VBA code.

HTH

 

[CasperA]

To do a)

Select columns B:H > open find and replace (ctrl + H) then under find what type (2 spaces) under replace with type (1 space) click replace all

To do b) Same as above

To do c) u might need a helper cell and do =LEFT(A1,LEN(A1)-1) (if data starts in A1)

Otherwise you would need VBA code.

HTH

Thank you! Would it be possible to assign hotkeys to the solution for a) and the solution for b) ? Or VBA-code perhaps?

CA

 

[Rick Rothstein]

Or VBA-code perhaps?

Give this macro a try...

Sub CleanUpColumnsBandH()
  Dim Addr As String, V As Variant
  For Each V In Array("B", "H")
    Addr = V & "1:" & V & Cells(Rows.Count, V).End(xlUp).Row
    Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(IF(RIGHT(@)=" & _
                  "CHAR(10),LEFT(@,LEN(@)-1),@)),"","",""""),"""")", "@", Addr))
  Next
End Sub

 

[CasperA]

Give this macro a try...

Sub CleanUpColumnsBandH()
  Dim Addr As String, V As Variant
  For Each V In Array("B", "H")
    Addr = V & "1:" & V & Cells(Rows.Count, V).End(xlUp).Row
    Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(IF(RIGHT(@)=" & _
                  "CHAR(10),LEFT(@,LEN(@)-1),@)),"","",""""),"""")", "@", Addr))
  Next
End Sub

Thank you, Rick. The code deletes the double spaces nicely, but leaves in the full stops at the end.

CA

 

[Rick Rothstein]

Thank you, Rick. The code deletes the double spaces nicely, but leaves in the full stops at the end.

At the end of what... the whole line or individual sentences?

 

[CasperA]

At the end of what... the whole line or individual sentences?

Some of the cells in columns B and H contain a last word ending with a full stop. Others contain no full stops. Others contain a full stop in for instance "Mr. Jones" - All full stops after the last word in a cell should be deleted, but not any other full stops. -> Mr. Jones should remain Mr. Jones but Mr. Jones. should become Mr. Jones

CA

 

[CasperA]

Cell H1:

Please contact[double space]Mr. Jones.

Desired output:

Please contact[single space]Mr. Jones

[=double space between contact and Mr. removed, full stop after Jones removed, and . after Mr not removed]

Cell H2:

Contact our Sales Department.

Desired output:

Contact our Sales Department

[full stop after Department removed]

 

[Rick Rothstein]

Ah, I see the problem... you said "full stop" and in my mind's eye I saw "Line Feed". Don't ask me why, that is just how I read it. Anyway, give this code a try, it should do what you want...

Sub CleanUpColumnsBandH()
  Dim Addr As String, V As Variant
  For Each V In Array("B", "H")
    Addr = V & "1:" & V & Cells(Rows.Count, V).End(xlUp).Row
    Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(IF(RIGHT(@)="".""" & _
                           ",LEFT(@,LEN(@)-1),@)),"","",""""),"""")", "@", Addr))
  Next
End Sub

 

[CasperA]

Ah, I see the problem... you said "full stop" and in my mind's eye I saw "Line Feed". Don't ask me why, that is just how I read it. Anyway, give this code a try, it should do what you want...

Sub CleanUpColumnsBandH()
  Dim Addr As String, V As Variant
  For Each V In Array("B", "H")
    Addr = V & "1:" & V & Cells(Rows.Count, V).End(xlUp).Row
    Range(Addr) = Evaluate(Replace("IF(LEN(@),SUBSTITUTE(TRIM(IF(RIGHT(@)="".""" & _
                           ",LEFT(@,LEN(@)-1),@)),"","",""""),"""")", "@", Addr))
  Next
End Sub

It sure does, Rick. Impressive, thank you very much!

CA