code to create username based on another cell

jachbo

New Member
Joined
Aug 31, 2014
Messages
4
I know this has been answered but I did not understand what to do.

I need to create usernames from another column.

Column A will be the username.

Column C is where the data is, which is the companies actual name.

I need to pull the first 8 digits in the company name and have it go to Column A, whatever row.

Some Companies have multiple words in their name, but I just need the first 6, and then a random 3 digits (numbers/symbols combination) to allow for Companies with the same name or that have multiple locations.

I have thousands of companies so need this to be done automatically.

They will not be logging into the spreadsheet, I am creating users for a website and will be doing a CSV import from this sheet.

I wanted to thank you for this forum, I found the VBA code to create password and it works like a charm.
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
These two statements conflict:
I need to pull the first 8 digits in the company name and have it go to Column A, whatever row.
Some Companies have multiple words in their name, but I just need the first 6, and then a random 3 digits (numbers/symbols combination) to allow for Companies with the same name or that have multiple locations.
The following code will produce an eight character entry in column A using the first five characters of the cell in column C of that row concatenated to a randomly generated number between 100 and 999.
Code:
Sub createCodeName()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = sh.Range("C2:C" & lr)
    For Each c In rng
        If Len(c) > 5 Then
            Randomize
            c.Offset(0, -2) = Left(c, 5) & Int((999 - 100) * Rnd + 100)
        Else
            Randomize
            c.Offset(0, -2) = c & Int((999 - 100) * Rnd + 100)
        End If
    Next
End Sub
 
Upvote 0
So I put this into the sheet by hitting F11 and insert module?

What do I do after that?

Do I need to put a function into the column I want this data to go to, which would be column 1 row 2, and then down of course.

Sorry for being so dumb

Candi
 
Upvote 0
So I put this into the sheet by hitting F11 and insert module?

What do I do after that?

Do I need to put a function into the column I want this data to go to, which would be column 1 row 2, and then down of course.

Sorry for being so dumb

Candi

To use the code:
1. Open the VB Editor by pressing Alt + F11 function key.
2. Copy the code from this thread into code module1 on the VBE screen.
3. Close the VBE screen
4. Save the workbook as a Macro Enabled workbook (.xlsm)
5. Press Alt + F8 function key.
6. Double click the macro name, or left click the macro name and then click 'Run'
 
Upvote 0
Two other other questions

If I want to add a hyphen between the word string and number string how would I do that.

How to change the number string to 5 in length with all types of characters: letters, numbers and characters
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top