I have come across situations where the cell is formatted as text and
the content is something like ''123 (i.e. double apostrophe, but shows
up as '123). Here, you have to use a compound formula in the helper
column, as follows:

=VALUE(RIGHT(A1,LEN(A1)-1))

Hope this helps.

Pete