On Tue, 18 Jul 2006 20:21:25 GMT, "JoeSpareBedroom" <dishborealis@yahoo.com>
wrote:

>I have a column of UPC numbers, most of which are 10 characters, which is
>all we need for our purposes. But, some contain an 11th check digit at the
>beginning, which we do NOT want, since it interferes somewhat with reading
>accuracy.
>
>Examples:
>Some look like: 3600012345
>Others look like: 73600012345
>
>I want to remove the 7, and also split the remaining 10 digits with a dash,
>so we get this:
>36000-12345
>
>No calculation is ever done with these characters - they're used only as
>text. I know how to use the RIGHT() & LEFT() functions, so I can strip off
>the chars I want, but I do NOT know how to have Excel check to see if there
>are 11 digits and automatically get rid of the first one. I can do it in
>Access or Paradox, but I'd rather not add an extra chore, since I'll be
>receiving these files once a week. I suppose I could sort the whole list,
>which would put all the 11-char string in one place, and then use two
>different string formulae to handle the different groups, but that's not
>very elegant.
>
>Help!
>


=TEXT(RIGHT(A1,10),"00000-00000")


--ron