There must be a way... Concatenate?

[ShoesNBlues]

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!

 

[sailepaty]

It would be easier if you provide us an example like this:

a) 17-Digit UCC number (Example)

b) The result expected after the first part of your formula (Add all of the digits in odd position then multiply by 3)

c) The result expected after the second part of your formula (Add all of the evens)

d) The result expected after the third part of your formula (Add the two products together)

c) Finally the check digit the formula should be returned.


Regards

 

[ShoesNBlues]

Not the check digit...

That's the thing... I have found my own formula for the check digit. I don't need help with that.

Because of the formula I used, I needed to put each individual digit into its own cell. I need the sequence of digits (the number in its entirety) to be broken up so each digit is in its own cell.

Think of the number 12345: 1 goes in a cell; 2 goes in a cell; 3 goes in a cell; 4 goes in a cell; 5 goes in a cell. After that comes the number 12346. I need 1 in a cell, 2 in a cell, 3 in a cell, 4 in a cell, and 6 in a cell.

When I use concatenate, its close, but it obviously returns the entire number in stead of just one digit.

Do you see my problem now?

 

[PaddyD]

with your 17 digit code in a1:

=MID($A1,COLUMN()-1,1)

..& copy across - though it's unclear, to me at least,why you persist with this approach, given potential solutions that don't require you to duplicate & re-arrange your data.