How to combine these 11 cells into one cell for upc formula?

Rocky2889

New Member
Joined
Feb 1, 2005
Messages
14
Currently I am using this formula for UPC code generating. But the problem is I have all digit 11 cells. Is there anyway to combine into cell. Here is the formula.

=MOD(10-(MOD(((A1+C1+E1+G1+I1+K1)*3)+(B1+D1+F1+H1+J1),10)),10)

Thanks

Rocky
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Rocky2889

New Member
Joined
Feb 1, 2005
Messages
14
What data is in cells A1 -> K1? What is your MOD formula trying to do?

The data is just like this

a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1
2 6 8 2 6 8 1 2 3 4 5

All the cells data is number. the result of the formula is 26826812345 plus a checksum digit.

Thanks

Rocky
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
Rocky.

I'm sorry I still don't understand. The formula only spits out a single digit - in the example provided, this is 3.

If you want to combine your individual cell entries into a single cell, use

=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1

This gives you a text value - for the inputs you provided, 26826812345.

If you want the Mod formula output included, simply add the cell address into the formula above. Or, you could say

=(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1)&MOD(10-(MOD(((A1+C1+E1+G1+I1+K1)*3)+(B1+D1+F1+H1+J1),10)),10)

If none of this is what you want to do, I guess try me again?

Sorry to be dense. Ben.
 

Rocky2889

New Member
Joined
Feb 1, 2005
Messages
14

ADVERTISEMENT

Ben,

I try to enter these numbers 26826812345 into one cell. Instead of entering #2 in cell a1, #6 in cell b1 and #8 in cell c1 and so on.

Saving from type each cell number.

Your result of 3 is correct.

Thanks

Rocky
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
Rocky.

Ok, I'm finally with you, and I think I have what you want

1) Enter in cell A1: 26826812345
2) Enter in cells B1->J1: =MID(A1, X, 1), where X = 1 for ColB, 2 for ColC, etc.
3) Enter in Cell M1: Your Mod Formula =MOD(10-(MOD(((B1+D1+F1+H1+J1+L1)*3)+(C1+E1+G1+I1+K1),10)),10)

Step 2 tells Excel to find the number in position X in A1 and put it in the appropriate column.

Hope that helps. Ben.
 

Rocky2889

New Member
Joined
Feb 1, 2005
Messages
14

ADVERTISEMENT

Ben,

My formula is used to generate UPC code. I follow this sample code accepts to calculates the check digit, and returns the finished string. The general method involves adding up the digits in the odd-numbered positions and multiplying that sum by 3; then adding to that result the values of the digits in the even-numbered positions. Divide this result by 10 and subtract the remainder from 10. If the result is equal to 10, set it to zero; otherwise this is the check digit.

Because I don't know how to put forumla in odd number positions and even number positions. May be you reading the about requirement you can put into a formula.

Thanks

Rocky
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
Rocky.

1) Enter in cell A1: 26826812345
2) Enter in cells B1->L1: =MID(A1, X, 1), where X = 1 for ColB, 2 for ColC, etc.
3) Enter in Cell M1: Your Mod Formula =MOD(10-(MOD(((B1+D1+F1+H1+J1+L1)*3)+(C1+E1+G1+I1+K1),10)),10)

The spreadsheet set-up described above

1) takes a UPC code from cell A1
2) breaks it up into 11 cells (B1->L1)
3) and then checks the validity of the UPC code via your formula in cell M1. if cell M1 = 0, then you have a valid UPC code.

The assumption I made here was that you actually had data to enter into cell A1. Are you saying that you DON'T have a UPC? Your earlier posts say that you want to generate a UPC code -- but I don't see why you provided a UPC number (26826812345).

Again, sorry to be so confused.
Ben.
 

Rocky2889

New Member
Joined
Feb 1, 2005
Messages
14
Ben,

Sorry making it so confusing, I attached the excel sheet for you to check.
Basically I like to be able to create consenstive number in column A and generative UPC code on column E.

On Column C is the checksum formula.



upc.jpg
(alt[/img]


Thanks

Rocky
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
Rocky.

Is this what you want?

Ben.

'edited by Admin to remove HTML Maker code currently out of commission
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

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