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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
"...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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top