Replace "+" with ":"

[lukerees83]

In one column of each sheet on my workbook some of the times are written as 21+34 and some are written as 21:34. Don't ask!

To do calculations obviously the + needs to be replaced with a : and I have previously done this on Excel 2003 with no problem, it was a simple find and replace.

I am now on Excel 2011 for Mac and when I do the same thing it says 'the formula you have entered contains an error" etc etc....

It also suggests that if you are not entering a formula you should simply put a single quotation mark ' before the terms you are finding an replacing. So I typed '+ and '+ (I also tried '+' and ':' just in case). In this case it says no match could be found.

How can I make it do this simple F&R?

 

[Derek Brown]

Have you tried a tilde (~) character in front of the character that you want to replace? That used to be the relevant 'escape' character to use.

 

[Sektor]

VBA

With Range("A1")
    .Value = Replace(.Value, "+", ":")
End With

 

[lukerees83]

I've never used any VBA before, where do I paste it into?

 

[kpark91]

You might not have developer tab not enabled, so you might need to go to Excel Options and enable the tab.
Then go to developer tab -> Visual Basic

and then you'll see on the left menu of your sheets,
double click the sheet you want to do the replacing in...
then jut copy and paste the code above into a sub.

Sub CustomReplace()
    With Range("A1")
    .Value = Replace(.Value, "+", ":")
    End With
End Sub

I'm not too sure if XL2011 for mac has VBA tho...