VBA to replace first character in cell on multiple sheets

[cruyff73]

Hi,

I've got a workbook with multiple sheets. All sheets have identical headers

All cells in column G contain telephone numbers beginning with 0.

Is there any way of cycling through all sheets and changing the first 0 in each cell with +44?

Thanks

 

[Trevor G]

Assuming that column G is text formatted or custom number formatted you could try this code:

Sub Macro2()
For Each ws In Worksheets
ws.Activate
Columns("G:G").NumberFormat = """+44""#"
Next ws
End Sub

 

[cruyff73]

Thanks Trevor,
The code worked on sheet 1 but not the others for some reason

 

[cruyff73]

Just noticed that if the cells are stored as text and it works if I convert them to numbers. Is there a way of adding this into the vba to do it all in one hit?

 

[Trevor G]

This should work, it is changing the format of the cells to number format as it moves to each sheet. Any cell that is formatted to numbers would loose a leading zero anyway.