Remove first character from a cell when its a specific value

[peakoverload]

I have created a workbook that generates an invoice number and saves as a new workbook in folders based on data entered into the workbook. It's worked fine for a year but suddenly we are finding that one cell (D1), where we enter a title of a book, it's adding a ' at the start even though we didn't type it in and this is causing lots of problems because other macros use this cell to create a new folder to save it in.

Coping and pasting another empty cell over D1 fixes it temporarily but it keeps coming back. I thought it was because the person that uses it most was pasting in the title from another document where titles are often listed between ' ' but they are adamant that they aren't doing this and are manually typing it in. Whilst I may have my doubts about what they are saying I'm also trying to find a solution so I was wondering if I could automatically delete the ' if it is the first character in a cell.

I tried entering =IF(LEFT(D1,1)="'",RIGHT(D1,LEN(D1)-1),D1) into a blank cell but find two things.

1. Whilst the leading ' is removed from the cell, it still shows in Formula Bar
2. Whatever I type into D1 also appears in the cell where I entered this formula

Does anyone know why a leading ' is appearing all the time or how I can write a formula or macro that will automatically remove it when it is the first character?

[Sam Capricci]

I noticed that if I apply the clean function to the cell with a leading ' it will remove it, BUT you will need to use another cell (helper cell).
so I put 'cat in cell A7, then =clean(A7) in B7 then copied the result in B7 to D7 as paste special >> values and it returned cat without the '

[peakoverload]

Ahh okay thank you. I've done as you've suggested and that seems to work. Many thanks for that, much appreciated.

[Sam Capricci]

glad I could help, and THANK you for the rep!