# There must be a way... Concatenate?

#### ShoesNBlues

##### New Member
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

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

#### Barrie Davidson

##### MrExcel MVP
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
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
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

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

##### MrExcel MVP
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

=(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

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

##### MrExcel MVP
"...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
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
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.

Replies
12
Views
825
Replies
7
Views
133
Replies
1
Views
153
Replies
9
Views
348
Replies
12
Views
191

### Forum statistics

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.

### Which adblocker are you using?    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

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