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...

Not sure which way you want it, but you can either do

="H"&TEXT(A1,"000000")

or

=--MID(A1,2,6)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sean" wrote in message
...
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...

brilliant thanks

"Bob Phillips" wrote:

Not sure which way you want it, but you can either do

="H"&TEXT(A1,"000000")

or

=--MID(A1,2,6)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sean" wrote in message
...
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...