+ Reply to Thread
Results 1 to 4 of 4

Excel Function to Create Code

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Excel Function to Create Code

    Hi,

    I am trying to create a code based on text using excel functions. I have a long list of names that I want to change to something meaningless. The names appear more than once in the list but I want it to return the same answer for each occurrence. I'd also like the length of the code to match the length of name:

    Fred Blegs = XDAF GKAZT
    Jon Smith = NIC TSQWP


    Is this possible? If so how? Should I think about VBA?

    Any suggestions would be much appreciated.

  2. #2
    Registered User
    Join Date
    04-19-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Excel Function to Create Code

    Its perfectly possible, in fact I built a working example, it works for names up to 30 characters long including spaces.

    It picks out each character in a string converts it using the code() function, adds 6 then changes it back to a character using char()

    Unhide columns c - af and you should be able to see how it works pretty easily and extend the number of characters it can manage etc,

    Also you can use the exact same sheet to reverse the encoding by changing +6 to -6 (or w/e you choose to use)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-10-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Excel Function to Create Code

    SigFreid, That is Brilliant! Thank you so much!!

    I've just worked it out using a VBA macro but I prefer your solution.

    This is the code I wrote (in case you are interested):

    Please Login or Register  to view this content.
    In my lookup range I have 2 cols - A-Z in column 1 with Z-A in column 2 and a space.

    Once again thank you very much.

  4. #4
    Registered User
    Join Date
    04-19-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: Excel Function to Create Code

    Glad to be of service!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1