+ Reply to Thread
Results 1 to 6 of 6

Creating a loop for information

  1. #1
    Registered User
    Join Date
    12-11-2010
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    8

    Creating a loop for information

    Hi,

    Yesterday I posted this thread (http://www.excelforum.com/excel-prog...ml#post2433252) asking about assigning numeric values to letters and calculating a letter 5 letters away for the original.

    The code given in response works perfectly, but when I get to the letter "V" it starts going into the symbols. Is there a way to make the it start back at A instead of going into the symbols? For example, V+5 = A, W+5 = B, and so on.

    Thanks in advance
    Last edited by Weeman975; 12-12-2010 at 08:37 PM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Creating a loop for information

    Hi Weeman975
    long but would do the trick
    =CHOOSE(CODE(LOWER(A1))-96,"F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","A","B","C","D","E")
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    12-11-2010
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Creating a loop for information

    Pike,

    Thank you so much for that. it works perfectly.

  4. #4
    Registered User
    Join Date
    12-11-2010
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Creating a loop for information

    Quote Originally Posted by pike View Post
    Hi Weeman975
    long but would do the trick
    =CHOOSE(CODE(LOWER(A1))-96,"F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","A","B","C","D","E")
    Sorry Pike, but I have another question. What does the 96 mean? Also, would there be a way of having the formula increase by the a value in a cell rather than the set number?

    Thanks heaps

  5. #5
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Creating a loop for information

    Hi Weeman975
    This is what you want
    where a1 id the letter
    and a2 is the increase value

    =UPPER(CHAR(IF(CODE(LOWER(A1))+A2>122,CODE(LOWER(A1))+A2-26,CODE(LOWER(A1))+A2)))

  6. #6
    Registered User
    Join Date
    12-11-2010
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Creating a loop for information

    Pike,

    Thank you so much. You are a genius. I wish I knew half the things about excel that you do.

    Thanks so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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