Solved: Adding and apostophe to the beginning of numbers within cell

Hi

We currently have an Excel spreadsheet containg various info.

In column E we have a unique identifier in the form of 9 digits, these are still all preceded by zeros i.e. 000324555

What happens is we have to copy the data into another program and unless all of these zeros have been preceded by an apostrephe i.e. '00324555 then it is copied over as 324555 (not correct)

Does anyone know of a basic formula or macro that will allow me to put an apostrophe at the start of each number within that column (I suppoes it should ensure that there isn't already an apostophe there already)

Thanks for your help

[vba]
For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row

Cells(i, "E").Value = "'" & Cells(i, "E").Text
Next i
[/vba]

Bob,

Would it help to add code so the macro adds a leading apostrophe only if there isn't one there already? What about placing the code as part of a worksheet_changeeevent() so he doesn't have to remember to run the macro? (Am I gilding the lily?)

Cheers,

Ron,

Certainly makes sense to check for the apostrophe first. I don't think event code is necessarily the best way though as he said it needs to copy it to another program, so a button driven macro seems good to me.

Bob,

Point taken, makes sense.

Thanks,

Hi Bob,

as usual - works perfectly.

Thanks for the help and for the input from Ron.

This was purely a one off to help a colleague so I am more than happy to hold it as a macro button.

Thanks Again

Gerry