Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
-------------------------------------------------------------------------------- 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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 "Louis" wrote: -------------------------------------------------------------------------------- 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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()*2 6)+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),CHA R(INT(RAND()*10)+48))&IF(ROUND(RAND(),0)=0,CHAR(IN T(RAND()*26)+65),CHAR(INT(RAND()*10)+48))&D1 Told you it was ugly. :O HTH, Gary Brown "Louis" wrote in message ... -------------------------------------------------------------------------------- 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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 Claud Balls" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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 "Gary Brown" wrote: 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) ..... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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 "Gary Brown" wrote: 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) ..... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
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. "LDanix" wrote: 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 "Gary Brown" wrote: 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) .... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Field inputs from other fields
To get the hyphen:
A4=B1&B2&B3&B4&"-"&B5&B6........... "LDanix" wrote: 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 "Gary Brown" wrote: 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) .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change all - fields to + field | Excel Discussion (Misc queries) | |||
copy field settings of a field to other fields in same pivot tbl | Excel Worksheet Functions | |||
One field into many fields ? | Excel Worksheet Functions | |||
pivot field of fields | Excel Worksheet Functions | |||
Copying field inputs | Excel Discussion (Misc queries) |