I have a spreadsheet that contains reference number in a format
H123456 (H followed by 6 numbers) for example H005678 I would need to
convert this to 5678. The original cell has been typed in full and is not a
format.

where the number ie 5678 is less than 6 numbers leading zero's have been
included.

I need to do a look up from my original spreadsheet into this new one.
Where my spreadsheet will say 5678 ( and I Can't change) I need to look
this up in the new spreadsheet where it will say H005678 it will possibly
easier to do convert the H005678 in the new SS and then do a look up
Can anyone help
Sean...