Brain Clog! Help with formula, please

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!

Someone will probably have an easier solution than this but I'd head for
something like the following (assuming A3 is your raw data):

=IF(LEN(A3)=10, LEFT(A3,5) & "-" & RIGHT(A3,5), MID(A3, 1,5) & "-" &
RIGHT(A3,5))

It's another column but my personal preference is to leave the orginal
data out there so I can check myself later if need be.

"JoeSpareBedroom" <dishborealis@yahoo.com> wrote in
news:9pbvg.5612$oa1.1467@news02.roc.ny:

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

"PC" <pc@i.com> wrote in message news:Xns98049DAAF4592pcicom@70.168.83.30...
> Someone will probably have an easier solution than this but I'd head for
> something like the following (assuming A3 is your raw data):
>
> =IF(LEN(A3)=10, LEFT(A3,5) & "-" & RIGHT(A3,5), MID(A3, 1,5) & "-" &
> RIGHT(A3,5))
>
> It's another column but my personal preference is to leave the orginal
> data out there so I can check myself later if need be.

I don't mind the extra column. The sheet's also got a bunch of extraneous
data we don't need, so once I have a working formula, I'll copy & paste the
relevant stuff to a neater looking sheet.

More than one solution but how about

=MID(D5,LEN(D5)-9,5)&"-"&RIGHT(D5,5)

where D5 contains the 10 or 11 digit number

"JoeSpareBedroom" <dishborealis@yahoo.com> wrote in message
news:9pbvg.5612$oa1.1467@news02.roc.ny...
>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!
>

One option would be to use:
=MOD(A1,1e10)

and custom format as "00000-00000"

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"JoeSpareBedroom" <dishborealis@yahoo.com> wrote in message
news:9pbvg.5612$oa1.1467@news02.roc.ny...
>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!
>

Whoah....there's a function I've never played with. Stay tuned - I'll be
back after making dinner, mowing the lawn, and who-knows-what-else. Thanks
to everyone so far.


"Dana DeLouis" <ddelouis@bellsouth.net> wrote in message
news:uO7F9qqqGHA.1976@TK2MSFTNGP04.phx.gbl...
> One option would be to use:
> =MOD(A1,1e10)
>
> and custom format as "00000-00000"
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "JoeSpareBedroom" <dishborealis@yahoo.com> wrote in message
> news:9pbvg.5612$oa1.1467@news02.roc.ny...
>>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!
>>
>
>

For effectiveness and brevity, we have two winners:

Dana, for =MOD(A1,1e10), although I haven't a clue why the thing works.

And, Ron Rosenfeld for =TEXT(RIGHT(A1,10),"00000-00000")

Thanks to everyone who offered suggestions.



> One option would be to use:
> =MOD(A1,1e10)
>
> and custom format as "00000-00000"
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "JoeSpareBedroom" <dishborealis@yahoo.com> wrote in message
> news:9pbvg.5612$oa1.1467@news02.roc.ny...
>>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!
>>
>
>

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