macro replace a string with certain text..?

[adbasanta]

Good day to all!

Im just having a little problem on how to replace a certain string characters with new format via macro.

Example:

cbu-juan, dela cruz

i would like to make it to

cbu - juan, dela cruz

the changes is that, there is a space before and after the dash.

thank you..

[Domski]

What code do you have so far? The macro recorder would give you the syntax you need using a Find...Replace.

Dom

[Kyle123]

How about:

newString = Replace(OldString,"-"," - ")

[Firefly2012]

If you already have values within the sheet which have the correct " - " structure, then you may want to TRIM the result eg:

newString = Application.Trim(Replace(OldString,"-"," - "))

This ensures you don't end up with multiple spaces either side of the -

[adbasanta]

I have this code as you guys suggested, but it seems that the whole content of the column has been erased.

Sub replace_str_format()
Dim counter As Long
    For counter = 1 To 131
        With Sheet1
            .Cells(counter, 1) = Trim(Replace(OldString, "-", " - "))
        End With
    Next
End Sub

it should only find the "-" in a word and replace it with " - "

thank you

[adbasanta]

sorry guys..I can't believe I missed to replace the oldstring variable with mine..
here is the modified version.

Sub replace_str_format()
Dim counter As Long
    For counter = 1 To 131
        With Sheet1
            .Cells(counter, 1) = Trim(Replace(.Cells(counter, 1), "-", " - "))
        End With
    Next
End Sub

Thank you Domski, Kyle123, Firefly2012 for such a straight to the point solution.

[Firefly2012]

Remark: you need to use Application.Trim as this is the worksheetfunction version that converts "dog Cat" to "dog Cat" ie internal multiple spaces are trimmed. You are currently using the VBA version which will only trim leading/trailing spaces (ie internal ones are ignored).

[adbasanta]

Remark: you need to use Application.Trim as this is the worksheetfunction version that converts "dog Cat" to "dog Cat" ie internal multiple spaces are trimmed. You are currently using the VBA version which will only trim leading/trailing spaces (ie internal ones are ignored).

thank you Firefly2012, but how to convert this code to application.trim? I cannot use the trim with application since it will not show as a function of the application when i press dot..
here is code so far.

Sub replace_str_format()
Dim counter As Long
    For counter = 1 To 131
        With Sheet1
            .Cells(counter, 1) = Trim(Replace(.Cells(counter, 1), "-", " - "))
        End With
    Next
End Sub

[Firefly2012]

thank you Firefly2012, but how to convert this code to application.trim? I cannot use the trim with application since it will not show as a function of the application when i press dot..
here is code so far.

Just because it doesn't show up in Intellisense doesn't mean you can't use it (if this was the case, you'd be screwed every time you tried to use Activesheet)

Did you try it with Application.Trim?

[adbasanta]

Just because it doesn't show up in Intellisense doesn't mean you can't use it (if this was the case, you'd be screwed every time you tried to use Activesheet)

Did you try it with Application.Trim?

Thank you Firefly2012. It works perfectly. I can't believe such Intellisense doesn't show-up. Im sorry but im not a hardcoder in Excel. My ground is VB, but I love to do it in excel to.Thank you..

Sub replace_str_format()
Dim counter As Long
    For counter = 1 To 260
        With Sheet4
            .Cells(counter, 3) = Application.Trim(Replace(.Cells(counter, 3), "-", " - "))
        End With
    Next
End Sub

[Domski]

If you don't already have " - " and can avoid the trim then this would suffice:

Range("A1:A131").Replace What:="-", Replacement:=" - ", LookAt:=xlPart

Dom

[adbasanta]

thank you Domski, will this work with internal "-"?.

[Domski]

Should do.

Dom

[adbasanta]

thank you Dom.

Should do.

Dom

[Kyle123]

This is probably a little faster - does away with the loop:

Dim rng As Range
Set rng = Sheet4.Range("C1:C260")
rng.Value = Split(Application.Trim(Replace(Join(Application.Transpose(rng), "|\"), "-", " - ")), "|\")

[adbasanta]

This is probably a little faster - does away with the loop:

Dim rng As Range
Set rng = Sheet4.Range("C1:C260")
rng.Value = Split(Application.Trim(Replace(Join(Application.Transpose(rng), "|\"), "-", " - ")), "|\")

thank you Kyle123. It seems the loading of this solution is faster, But when I execute the code, all values in column C has erased?

Sub format_str()
Dim rng As Range
Set rng = Sheet2.Range("C1:C260")
rng.Value = Split(Application.Trim(Replace(Join(Application.Transpose(rng), "|\"), "-", " - ")), "|\")
End Sub

Do I need to add some code in .Transpose area? It seems to be a complicated solution but its fast.