There must be a way... Concatenate?

ShoesNBlues

New Member
Joined
Oct 19, 2006
Messages
15
Hi!

I'm having a bit of a problem figuring out a formula for this:

-The spreadsheet I'm putting together is to automate 17-digit UCC numbers + their check digits. The formula to find the check digit is to add all of the digits in odd position then multiply by 3, then add all of the evens, then add the two products together; the check digit is what it takes to round up to the nearest 10. I figured that formula out with a little trial and error.

-What I had to do, because of the even and odd positions, was to put each digit in its own cell.

-I don't want to have to manually enter every digit into each individual cell, so I tried to concatenate, which kind of works, but not really because it returns the entire number, not one digit per cell.

Anybody have any idea how to do this?

Thanks!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Welcome to the Board!

Try this formula (assumes your UCC number is in A1):
Code:
=CEILING(LEFT(A1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)+MID(A1,13,1)+MID(A1,15,1)+MID(A1,17,1)+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)+MID(A1,12,1)+MID(A1,14,1)+MID(A1,16,1),10)-(LEFT(A1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)+MID(A1,13,1)+MID(A1,15,1)+MID(A1,17,1)+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)+MID(A1,12,1)+MID(A1,14,1)+MID(A1,16,1))

Does this work?
 

ShoesNBlues

New Member
Joined
Oct 19, 2006
Messages
15
Hi Barrie,

Thanks for the welcome and your attention!

It didn't work... and I made sure the full UCC number was in A1. Could you maybe explain the logic and I could go from there? I'm an Excel newbie (but fast becoming an Excel nerd- it's so fun!). What does the mid function do?

Becca
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Becca,
WELCOME to the BOARD !!!!!

watching this topic but I didn't reply because it was not clear what you mean exactly

Barries formula yields a result
so "not working" is not quite accurate

can you tell how it was not working
better would be to provide a UCC-number as entered in A1
then explain what the expected result is and how you get it step-by-step

kind regards,
Erik
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279

ADVERTISEMENT

Maybe,

=CEILING((MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1)+MID(A1,13,1)+MID(A1,15,1)+MID(A1,17,1))*3+(MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1)+MID(A1,12,1)+MID(A1,14,1)+MID(A1,16,1)),10)


Regards
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
As has been pointed out, you've not specified you problem very precisely, nor given us an example, so:

"-The spreadsheet I'm putting together is to automate 17-digit UCC numbers + their check digits. The formula to find the check digit is to add all of the digits in odd position then multiply by 3, then add all of the evens, then add the two products together; the check digit is what it takes to round up to the nearest 10. I figured that formula out with a little trial and error. "


"The formula to find the check digit is to add all of the digits in odd position then multiply by 3"

=(SUMPRODUCT(--(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),--(MOD(ROW(INDIRECT("1:"&LEN(A5))),2))))*3

"add all of the evens,"

=(SUMPRODUCT(--(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),--(NOT(MOD(ROW(INDIRECT("1:"&LEN(A5))),2)))))

"then add the two products together" - so let's assume you meant to multiply the above by 3 as well:

=(SUMPRODUCT(--(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),--(NOT(MOD(ROW(INDIRECT("1:"&LEN(A5))),2)))))*3

...sure you can do the rest...
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279

ADVERTISEMENT

That is cool PaddyD,

Using your formula on this way I got the same result that the formula I posted.

=CEILING(SUMPRODUCT(--(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)))*3+SUMPRODUCT(--(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(NOT(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)))),10)

Regards
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...sure you can do the rest..."

...& just in case you can't :)

=10-MOD(((SUMPRODUCT(--(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),--(NOT(MOD(ROW(INDIRECT("1:"&LEN(A5))),2)))))*3)+((SUMPRODUCT(--(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),--(MOD(ROW(INDIRECT("1:"&LEN(A5))),2))))*3),10)
 

ShoesNBlues

New Member
Joined
Oct 19, 2006
Messages
15
Thanks for the responses...

I guess I'll just keep doing trial and error. I don't really know how to explain it more concisely than I did.
 

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
This has worked for me. Assumes 17 digit number is in A1.
Code:
=A1&MOD(10-MOD(SUM(--MID(A1,{1,3,5,7,9,11,13,15,17},1))*3+SUM(--MID(A1,{2,4,6,8,10,12,14,16},1)),10),10)
Credit goes to Jay Petrulis see post here.
 

Forum statistics

Threads
1,137,333
Messages
5,680,881
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top