Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-19-2011, 01:25 AM
stnicholas81 stnicholas81 is offline [How To] Generate Alpha Numeric Values in Excel 2010 Windows 7 32bit [How To] Generate Alpha Numeric Values in Excel 2010 Office 2010 64bit
Novice
[How To] Generate Alpha Numeric Values in Excel 2010
 
Join Date: Jul 2011
Posts: 1
stnicholas81 is on a distinguished road
Default [How To] Generate Alpha Numeric Values in Excel 2010

Hi Guys,

I need some advice on generating random (no repeat) alpha numeric numbers in Excel for promotion code usage.

I have done this for numerals before but not for alpha numerals. I need 24,000 of these values.

Requirement:
- 8 values consisting of
- 2 alpha
- 6 numerals
- any sequence

Would greatly appreciate any form of advice.



Cheers,
Nic
Reply With Quote
  #2  
Old 07-25-2011, 01:31 AM
moonfish moonfish is offline [How To] Generate Alpha Numeric Values in Excel 2010 Windows 7 32bit [How To] Generate Alpha Numeric Values in Excel 2010 Office 2007
Novice
 
Join Date: Jul 2011
Posts: 3
moonfish is on a distinguished road
Default

Try a table like this one:

A1 =RANDBETWEEN(100000,200000)
B1 =CHAR(64+ROUNDUP(RAND()*26,0))
C1 same as B1
D1 =ROUNDDOWN(RAND()*7,0)
E1 =RANDBETWEEN(D1,6)
F1 =LEFT(A1,D1)&B1&MID(A1,D1+1,E1-D1)&C1&RIGHT(A1,6-E1)

A2 =A1+ROUNDUP(RAND()*50,0)

Copy everything down across 24,000 rows. Next, select column F, copy your selection and paste as values into an empty column.

The values in column A are always unique because the roundup function makes sure you add at least one. The letters are just random. Based on the values in columns D and E, they are inserted into the number at random locations.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
[How To] Generate Alpha Numeric Values in Excel 2010 Generate chart from list of text values knownunknown Excel 6 04-24-2013 01:56 AM
Generate Excel Speadsheet from Access, Web, or??? chrisalter Excel 0 07-12-2011 01:04 PM
How Do I Put Text Values Into A CSV That Excel Will Leave Along? eBob.com Excel 2 05-04-2011 07:01 AM
[How To] Generate Alpha Numeric Values in Excel 2010 Validation for length and numeric type chamsy Excel 1 10-22-2010 07:31 AM
Cannot generate 2003 Excel reports in IIS7 sword.fish Excel 0 02-22-2010 01:43 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:58 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft