Generating Field inputs from other fields

[Louis]

--------------------------------------------------------------------------------

Hi,

Is it possible to generate a 8character code from the inputs in other
fields, I'd really like to be able to do something like the following:

A1 - Jims Field
D1 - 1 (category)

B1 code:

[A1 CODE-FIRST 3 LETTERS][4 RANDOM CHARACTERS BETWEEN A-Z AND 0-9][D1 CAT ID
1]

Can this be done? I have no idea where to start.

 

[Guest]

Enter in B1 and copy down as far as needed:

=LEFT(A1,3)&CHAR(INT(RAND()*25+65))&CHAR(INT(RAND()*25+65))&CHAR(INT(RAND()*25+65))&D1

 

[Gary Brown]

Louis,

The main (ugly) portion is the random part.

There are 3 parts put together with an ampersand (&).

Reference to A1 is...

LEFT(A1,3)

Reference to D1 is...

D1

The formula for the random part that generates a random number or letter
is...

IF(ROUND(RAND(),0)=0,CHAR(INT(RAND()*26)+65),CHAR(INT(RAND()*10)+48))

Therefore, in B1 should be the formula...

=LEFT(A1,3)&IF(ROUND(RAND(),0)=0,CHAR(INT(RAND()*26)+65),CHAR(INT(RAND()*10)+48))&IF(ROUND(RAND(),0)=0,CHAR(INT(RAND()*26)+65),CHAR(INT(RAND()*10)+48))&IF(ROUND(RAND(),0)=0,CHAR(INT(RAND()*26)+65),CHAR(INT(RAND()*10)+48))&IF(ROUND(RAND(),0)=0,CHAR(INT(RAND()*26)+65),CHAR(INT(RAND()*10)+48))&D1

Told you it was ugly. :O>

HTH,

Gary Brown

 

[Claud Balls]

Isn't doesn't the random number change every time you change cells? If
it does, I wouldn't think it would make a great index, as it would
always be changing.

 

[Gary Brown]

Claud,
Louis never said anything about an index. Yes it recalculates every time
the worksheet changes. If he wants to keep it static after one shot, he can
easily change it to a value

 

[Guest]

Gary-

I have a question about selecting characters from another cell. To get the
first three characters you stated to enter the forumla =LEFT(A1,3). My
question is how would I go about skipping the second character; and if I had
5 charaters, how would I skip the second and fourth? I'm wanting each cell to
have every-other character. And to make it more complicated, I have data from
three cells generating the final figure, and I need a dash after every four
characters. Here's an example:

A1= "Christopher" (Right to Left in formula)
A2= "09" (Left to Right in formula)
A3= "538" (Right to Left in formula)
(forumla needed for A4) A4= R0H9-O8S3-R5C

The idea here is to create serial numbers that make sense to me, but not
necessarily anyone else.

Thanks,
Luke

Louis,

The main (ugly) portion is the random part.

There are 3 parts put together with an ampersand (&).

Reference to A1 is...

LEFT(A1,3)

.....

 

[Guest]

Gary-

I have a question about selecting characters from another cell. To get the
first three characters you stated to enter the forumla =LEFT(A1,3). My
question is how would I go about skipping the second character; and if I had
5 charaters, how would I skip the second and fourth? I'm wanting each cell to
have every-other character. And to make it more complicated, I have data from
three cells generating the final figure, and I need a dash after every four
characters. Here's an example:

A1= "Christopher" (Right to Left in formula)
A2= "09" (Left to Right in formula)
A3= "538" (Right to Left in formula)
(forumla needed for A4) A4= R0H9-O8S3-R5C

The idea here is to create serial numbers that make sense to me, but not
necessarily anyone else.

Thanks,
Luke

Louis,

The main (ugly) portion is the random part.

There are 3 parts put together with an ampersand (&).

Reference to A1 is...

LEFT(A1,3)

.....

 

[Guest]

LDanix,

You will need to use the functions: Right(), Left(), and MID()
Use the Excel Help or the PasteFunction icon to get you started.
With long configurations like this, I find it much easier to create a new
column, say Col B (which I later hide). In this new col I enter the
components one cell at a time:
B1=Right(A1,1)
B2=Left(B1,1)
B3 =Mid(A1,2,1)
B4=Left(A2,1) etc
Then in A4 enter =B1&B2&B3...... to concatenate the results into one long
string.

 

[Guest]

To get the hyphen:

A4=B1&B2&B3&B4&"-"&B5&B6...........