Add white space before and after (@) ,If @ has adjacent character (before it or after)-CodePudding

I need to add white space before and after @ ,If @ has adjacent character (before it or after).
e.g with this strings new@test , new@ test , new @test the expected is new @ test.
e.g2 with @test the expected is @ test
e.g3 with new@ the expected is new @
the below code always replace @ regardless it has adjacent character or not.
thanks for your help.

Sub Macro1()
 
    ActiveCell.Replace What:="@", Replacement:=" @ ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

CodePudding user response：

You can use the following code to achieve what you want:

Public Function addSpace(ByVal strText As String, lookFor As String) As String

strText = Replace(Replace(strText, lookFor, " " & lookFor & " "), "  ", " ")
strText = Trim(strText)

addSpace = strText

End Function

A test routine would look like:

Public Sub test()
Dim rg As Range
Set rg = Selection

Dim c As Range
For Each c In rg
    c.Offset(, 1).Value = addSpace(c.Value, "@")
Next

End Sub

An example where A1:A5 was selected.

Based on @JvdVs comment - an optimized version:

Public Function addSpace(ByVal strText As String, lookFor As String) As String

'Using .WorksheetFunction.Trim according to https://stackoverflow.com/a/58454080/16578424
'@JvdV: https://stackoverflow.com/users/9758194/jvdv
'removes double spaces *within* string as well

With Application.WorksheetFunction
    strText = .Trim(Replace(strText, lookFor, " " & lookFor & " "))
End With

addSpace = strText

End Function

CodePudding user response：

I don't think this is a regex-question. See if you can apply worksheets functions. In the code below I used an implicit range object. Obviously you can adapt the code to be more explicit (or even make it as such that you ask the user for a active selection).

Before:

Code:

Sub Test()

Dim rng As Range: Set rng = Range("A1:B2")

With Application
    rng = .Trim(.Substitute(rng, "@", " @ "))
End With

End Sub

After:

Note: If your idea is to create a function I wouldn't go to these lenghts and even avoid VBA as native Excel functionality can be used:

=TRIM(SUBSTITUTE(A1,"@"," @ "))