Hi there,

i try to create a membercoding.. made out of the first 4 letters of the
lastname and a following number.

lastnames
jansen JANS1
jansen JANS2
janssen JANS3
jansons JANS4
jan JAN1

so when i type in a new member with the name

Jansma it generates the code JANS5

....

al also need this for access..
where i'm trying to create a big member database..

thanks for all the help

Sort then Ascending in column A

typ in colum B1 the correct short version.

type in B2
"=IF(LEFT(A2,4)=LEFT(B1,4),LEFT(A2,4)&MID(B1,5,2)+ 1,LEFT(A2,4)& 1)"

copy down.

regards

On Nov 9, 4:46*am, "Swingleft" wrote:
Hi there,

i try to create a membercoding.. made out of the first 4 letters of the
lastname and a following number.

lastnames
jansen * * * *JANS1
jansen * * * *JANS2
janssen * * *JANS3
jansons * * *JANS4
jan * * * * * * JAN1

so when i type in a new member with the name

Jansma it generates the code JANS5

...

al also need this for access..
where i'm trying to create a big member database..

thanks for all the help

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."