+ Reply to Thread
Results 1 to 4 of 4

Substitute Formula or Increment Help Please

  1. 03-12-2014, 10:15 PM #1
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Cool Substitute Formula or Increment Help Please

    Hi Guys,

    I have lots of sequence in hash format and I am trying to convert that into an another format for my research. Its a simple conversion. Here is what I want,

    I am trying to follow hexadecimal here.
    So 0-9 and a-f only.

    I would like to do the following conversion in excel. Column A is what I have and Column B is what I want->
    Please Login or Register  to view this content.
    I have tons of those sequence up to column AAA.

    I am after a formula that would make my work easy.

    Thanks in advance guys.

    Best Regards
    plasma33
    Reply With QuoteRegister To Reply
  2. 03-12-2014, 10:36 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Substitute Formula or Increment Help Please

    Hopefully I understood what you wanted (and if not, maybe helped point you in the right direction?)

    1st I created a table like this...
    E
    F
    G
    2
    0
    1
    3
    1
    2
    4
    2
    3
    5
    3
    4
    6
    4
    5
    7
    5
    6
    8
    6
    7
    9
    7
    8
    10
    8
    9
    11
    9
    0
    12
    a
    0
    b
    13
    b
    1
    c
    14
    c
    2
    d
    15
    d
    3
    e
    16
    e
    4
    f
    17
    f
    5
    f


    F12=MOD(CODE(E12)-97,6) copied down

    Then I used this in C2, copied down...
    =IF(ISNUMBER(A2),VLOOKUP(A2,$F$2:$G$11,2,0),VLOOKUP(MOD(CODE(A2)-97,6),$F$12:$G$17,2,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-12-2014, 11:17 PM #3
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Re: Substitute Formula or Increment Help Please

    Thanks Ford for your kind reply and the fix.

    I have got a solution with me. Its working perfectly as I want. Can you please comment on the formula that I have to see if its all good. Here is the formula that I am using->
    Please Login or Register  to view this content.
    Thanks again Ford.

    Best Regards
    plasma33
    Reply With QuoteRegister To Reply
  4. 03-13-2014, 12:15 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Substitute Formula or Increment Help Please

    wow thats quite a vlookup

    My 1st comment would be about the use of ""....if the numbers you are looking up are actual numbers (as opposed to text that looks like a number), then it will not find "1","2" etc, because the vlookup will consider them text.
    Also, if your letters go beyond f, you will need to extend that?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Substitute formula
    By sateesh konduru in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2014, 09:05 AM
  2. Help with a SUBSTITUTE formula...
    By chris0228 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2012, 03:37 PM
  3. [SOLVED] Substitute formula
    By manharji in forum Excel General
    Replies: 0
    Last Post: 02-13-2012, 02:27 PM
  4. How to use a substitute formula to substitute text entries to a different text entry
    By andybocchi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-01-2010, 07:50 PM
  5. Substitute Formula
    By AmyD in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2006, 09:40 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1