quick and easy one - creating usernames [SOLVED]

  • Hi


    I need to create usernames from a provided list, in the following format


    1. column A- First Name
    2. column B - Middle Initial
    3. Column C - Last Name


    THe user name has to be in the following format:
    firstname.middle initial.lastname


    here's the catch: the username cannot contain any characters outside the 26-letter alphabet set. (i.e. Pat O'Riley would be Pat.Oriley)


    Also, if No middle initial exists, it must be left unentered.
    i.e. John S. Andrews would be John.S.Andrews
    where as Jane Smith would be Jane.Smith
    (notice only 1 "." was used in the 2nd case)


    I have already done this with formulas, but its 7MB large, and very inefficeint. It checks letter by letter to see what it is, and it can do so for up to 51 letters


    I want to do this in a macro, but i'm not sure how........any ideas?

  • Hi djjb,


    Here's one way. using a user defined function to check and construct the username.


    It still checks each character.


    I'm not sure there is a quicker way. Testing all non valid characters could mean testing 255-(26*2) = 203 characters!



    You should be able to modify the function if you want it to be just a macro.


    Cheers
    Andy

  • This might help for part of what you want:



  • Thanks for the input guys,


    Little help, I'm quite new to macros, and I am not sure how User Defined Functions work. I tried Andy's code, not sure how to test it tho:(


    MAybe this isn't so quick and easy......

  • see the attached, using Andy Pope's code (nice work Andy :thumbcoo: ) - the code is in a module attached to the workbook


    user defined function - you use it just like any of XL's built in functions once defined in the VBE - just take note that ALOT of UDFs will slow your spreadsheet calculation time.


    Hope this helps

  • ^^^
    Great work Andy!!


    Code is stellar! Works really well, and sure is more effective then 7 MB.


    Thank you. and thank you WIll for the Clarification.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!