A1 contains:

8101940

=======================

B1 contains this formula:

=TEXT(A2,"000000000")

=======================

C1 contains this formula:

=MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1

======================

D1 contains this formula:

=RIGHT(C2,1)

======================

E1 contains this formula:

=B2&"-"&D2

which displays:

008101940-7

=============================================

How can I combine the 4 formulas so B2 contains the combination of the
4 formulas and diplays:

008101940-7

Hello,

If A1 contains 8101940 then put this formula into B2:

Beginning of the formula:
=TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
MID(TEXT(A1,"000000000"),ROW(1:9),1) * {1,3,7,9,1,3,7,9,1}))
End of the formula

If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
{1;3;7;9;1;3;7;9;1}

Hope this will help you.

(excel10+win7)




"gcotterl" <(E-Mail Removed)> a écrit dans le message de groupe
de discussion :
6b483341-172b-48d6-bcad-7c0194b36af9...oglegroups.com...
> A1 contains:
>
> 8101940
>
> =======================
>
> B1 contains this formula:
>
> =TEXT(A2,"000000000")
>
> =======================
>
> C1 contains this formula:
>
> =MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1
>
> ======================
>
> D1 contains this formula:
>
> =RIGHT(C2,1)
>
> ======================
>
> E1 contains this formula:
>
> =B2&"-"&D2
>
> which displays:
>
> 008101940-7
>
> =============================================
>
> How can I combine the 4 formulas so B2 contains the combination of the
> 4 formulas and diplays:
>
> 008101940-7

To copy the formula into others cells than B2 (ex: if you want to drag the
formula down your column B) it is better to replace
ROW(1:9) by ROW($1:$9)


"Charabeuh" <(E-Mail Removed)> a écrit dans le message de groupe de
discussion : i1b2ka$1fd$(E-Mail Removed)...
> Hello,
>
> If A1 contains 8101940 then put this formula into B2:
>
> Beginning of the formula:
> =TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
> MID(TEXT(A1,"000000000"),1) * {1,3,7,9,1,3,7,9,1}))
> End of the formula
>
> If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
> {1;3;7;9;1;3;7;9;1}
>
> Hope this will help you.
>
> (excel10+win7)
>
>
>
>
> "gcotterl" <(E-Mail Removed)> a écrit dans le message de groupe
> de discussion :
> 6b483341-172b-48d6-bcad-7c0194b36af9...oglegroups.com...
>> A1 contains:
>>
>> 8101940
>>
>> =======================
>>
>> B1 contains this formula:
>>
>> =TEXT(A2,"000000000")
>>
>> =======================
>>
>> C1 contains this formula:
>>
>> =MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1
>>
>> ======================
>>
>> D1 contains this formula:
>>
>> =RIGHT(C2,1)
>>
>> ======================
>>
>> E1 contains this formula:
>>
>> =B2&"-"&D2
>>
>> which displays:
>>
>> 008101940-7
>>
>> =============================================
>>
>> How can I combine the 4 formulas so B2 contains the combination of the
>> 4 formulas and diplays:
>>
>> 008101940-7
>

Hello.

I made an error: My formulas are in row A (not B)

=======

A1 contains 8101940

B1 contains your formula:

=TEXT(A1,"000000000") & "-" &
RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(1:9),1)
*{1;3;7;9;1;3;7;9;1}))

and 008101940-7 is displayed (THIS IS CORRECT).

========

B2 contains 209051010

But when I copy and paste your formula into B2, the formula changes
to::
:
=TEXT(A1,"000000000") & "-" &
RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(2:10),1)
*{1;3;7;9;1;3;7;9;1}))

and #VALUE! is displayed (instead of 209051010-2)

========

The only differences are in the "ROW" expressions:

In B1: it is: ROW(1:9),1)
In B2, it is: ROW(2:10),1)

How should I resolve this problem?

Gary

==========================================================


On Jul 10, 5:18*pm, "Charabeuh" <Ple...@FeedBack.fr> wrote:
> Hello,
>
> If A1 contains 8101940 then put this formula into B2:
>
> Beginning of the formula:
> =TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
> MID(TEXT(A1,"000000000"),ROW(1:9),1) * {1,3,7,9,1,3,7,9,1}))
> End of the formula
>
> If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
> {1;3;7;9;1;3;7;9;1}
>
> Hope this will help you.
>
> (excel10+win7)
>
> "gcotterl" <gcott...@co.riverside.ca.us> a écrit dans le message de groupe
> de discussion :
> 6b483341-172b-48d6-bcad-7c0194b36...@i19g2000pro.googlegroups.com...
>
>
>
> > A1 contains:
>
> > 8101940
>
> > =======================
>
> > B1 contains this formula:
>
> > =TEXT(A2,"000000000")
>
> > =======================
>
> > C1 contains this formula:
>
> > =MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(*B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1
>
> > ======================
>
> > D1 contains this formula:
>
> > =RIGHT(C2,1)
>
> > ======================
>
> > E1 contains this formula:
>
> > =B2&"-"&D2
>
> > which displays:
>
> > 008101940-7
>
> > =============================================
>
> > How can I combine the 4 formulas so B2 contains the combination of the
> > 4 formulas and diplays:
>
> > 008101940-7- Hide quoted text -
>
> - Show quoted text -

Hello,

You found the way to correct the formula :
It is to maintain ROW(1:9) as a contant array.

1) the first way is to replace ROW(1:9)
with {1,2,3,4,5,6,7,8,9}

2) another way is to replace ROW(1:9)
with ROW($1:$9)

Does this correct the problem ?






"gcotterl" <(E-Mail Removed)> a écrit dans le message de groupe
de discussion :
edefb4ab-21e5-41ce-b19b-d61d10154974...oglegroups.com...
> Hello.
>
> I made an error: My formulas are in row A (not B)
>
> =======
>
> A1 contains 8101940
>
> B1 contains your formula:
>
> =TEXT(A1,"000000000") & "-" &
> RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(1:9),1)
> *{1;3;7;9;1;3;7;9;1}))
>
> and 008101940-7 is displayed (THIS IS CORRECT).
>
> ========
>
> B2 contains 209051010
>
> But when I copy and paste your formula into B2, the formula changes
> to::
> :
> =TEXT(A1,"000000000") & "-" &
> RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(2:10),1)
> *{1;3;7;9;1;3;7;9;1}))
>
> and #VALUE! is displayed (instead of 209051010-2)
>
> ========
>
> The only differences are in the "ROW" expressions:
>
> In B1: it is: ROW(1:9),1)
> In B2, it is: ROW(2:10),1)
>
> How should I resolve this problem?
>
> Gary
>
> ==========================================================
>
>
> On Jul 10, 5:18 pm, "Charabeuh" <Ple...@FeedBack.fr> wrote:
>> Hello,
>>
>> If A1 contains 8101940 then put this formula into B2:
>>
>> Beginning of the formula:
>> =TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
>> MID(TEXT(A1,"000000000"),ROW(1:9),1) * {1,3,7,9,1,3,7,9,1}))
>> End of the formula
>>
>> If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
>> {1;3;7;9;1;3;7;9;1}
>>
>> Hope this will help you.
>>
>> (excel10+win7)
>>
>> "gcotterl" <gcott...@co.riverside.ca.us> a écrit dans le message de
>> groupe
>> de discussion :
>> 6b483341-172b-48d6-bcad-7c0194b36...@i19g2000pro.googlegroups.com...
>>
>>
>>
>> > A1 contains:
>>
>> > 8101940
>>
>> > =======================
>>
>> > B1 contains this formula:
>>
>> > =TEXT(A2,"000000000")
>>
>> > =======================
>>
>> > C1 contains this formula:
>>
>> > =MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(*B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1
>>
>> > ======================
>>
>> > D1 contains this formula:
>>
>> > =RIGHT(C2,1)
>>
>> > ======================
>>
>> > E1 contains this formula:
>>
>> > =B2&"-"&D2
>>
>> > which displays:
>>
>> > 008101940-7
>>
>> > =============================================
>>
>> > How can I combine the 4 formulas so B2 contains the combination of the
>> > 4 formulas and diplays:
>>
>> > 008101940-7- Hide quoted text -
>>
>> - Show quoted text -
>

perhaps you should replace {1,2,3,4,5,6,7,8,9}
with {1;2;3;4;5;6;7;8;9}



"Charabeuh" <(E-Mail Removed)> a écrit dans le message de groupe de
discussion : i1b69i$9cp$(E-Mail Removed)...
> Hello,
>
> You found the way to correct the formula :
> It is to maintain ROW(1:9) as a contant array.
>
> 1) the first way is to replace ROW(1:9)
> with {1,2,3,4,5,6,7,8,9}
>
> 2) another way is to replace ROW(1:9)
> with ROW($1:$9)
>
> Does this correct the problem ?
>

On Jul 10, 6:20*pm, "Charabeuh" <Ple...@FeedBack.fr> wrote:
> Hello,
>
> You found the way to correct the formula :
> It is to maintain ROW(1:9) as a contant array.
>
> 1) the first way is to replace ROW(1:9)
> with {1,2,3,4,5,6,7,8,9}
>
> 2) another way is to replace ROW(1:9)
> with ROW($1:$9)
>
> Does this correct the problem ?
>
> "gcotterl" <gcott...@co.riverside.ca.us> a écrit dans le message de groupe
> de discussion :
> edefb4ab-21e5-41ce-b19b-d61d10154...@y12g2000prb.googlegroups.com...
>
>
>
> > Hello.
>
> > I made an error: *My formulas are in row A (not B)
>
> > =======
>
> > A1 contains 8101940
>
> > B1 contains your formula:
>
> > =TEXT(A1,"000000000") & "-" &
> > RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(1:9),1)
> > *{1;3;7;9;1;3;7;9;1}))
>
> > and 008101940-7 is displayed (THIS IS CORRECT).
>
> > ========
>
> > B2 contains 209051010
>
> > But when I copy and paste your formula into B2, the formula changes
> > to::
> > :
> > =TEXT(A1,"000000000") & "-" &
> > RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(2:10),1)
> > *{1;3;7;9;1;3;7;9;1}))
>
> > and #VALUE! is displayed (instead of 209051010-2)
>
> > ========
>
> > The only differences are in the "ROW" expressions:
>
> > In B1: it is: * ROW(1:9),1)
> > In B2, it is: * ROW(2:10),1)
>
> > How should I resolve this problem?
>
> > * * * * * * * * * * * * * * * * * ** * * * * * *Gary
>
> > ==========================================================
>
> > On Jul 10, 5:18 pm, "Charabeuh" <Ple...@FeedBack.fr> wrote:
> >> Hello,
>
> >> If A1 contains 8101940 then put this formula into B2:
>
> >> Beginning of the formula:
> >> =TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
> >> MID(TEXT(A1,"000000000"),ROW(1:9),1) * {1,3,7,9,1,3,7,9,1}))
> >> End of the formula
>
> >> If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
> >> {1;3;7;9;1;3;7;9;1}
>
> >> Hope this will help you.
>
> >> (excel10+win7)
>
> >> "gcotterl" <gcott...@co.riverside.ca.us> a écrit dans le message de
> >> groupe
> >> de discussion :
> >> 6b483341-172b-48d6-bcad-7c0194b36...@i19g2000pro.googlegroups.com...
>
> >> > A1 contains:
>
> >> > 8101940
>
> >> > =======================
>
> >> > B1 contains this formula:
>
> >> > =TEXT(A2,"000000000")
>
> >> > =======================
>
> >> > C1 contains this formula:
>
> >> > =MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2,4,1)*9+MID(B2,5,1)*1+MID(**B2,6,1)*3+MID(B2,7,1)*7+MID(B2,8,1)*9+MID(B2,9,1)*1
>
> >> > ======================
>
> >> > D1 contains this formula:
>
> >> > =RIGHT(C2,1)
>
> >> > ======================
>
> >> > E1 contains this formula:
>
> >> > =B2&"-"&D2
>
> >> > which displays:
>
> >> > 008101940-7
>
> >> > =============================================
>
> >> > How can I combine the 4 formulas so B2 contains the combination of the
> >> > 4 formulas and diplays:
>
> >> > 008101940-7- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

yes.