Results 1 to 10 of 10

Thread: Formula to recognize digits inside a series of numbers within the cell?

  1. #1

    Formula to recognize digits inside a series of numbers within the cell?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I would like to have a formula that recognizes a portion of the digits in Column A and produce a letter output if it meets the criteria. For example, if Col A has 358 in the number series then output is B, if Col A has 357 then output is C and so forth and so on. I have 17 output letters (A-Q). I have tons of data I need to apply this too. If have researched so many formulas. I am a novice when it comes to complex formulas so any help would be greatly appreciated. Thanks! Brenda

    col A
    Col B Col C Col d Col E
    916 - 35806 35806 AL7414 2499938 7/4/14 $7.26
    3246 - 35758 35758 AL7414 2499966 7/4/14 $6.50
    1038 - 36830 36830 AL7414 2500165 7/4/14 $0.82
    586 - 35976 35976 AL7414 2500170 7/4/14 $3.15

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    So if the 3 digits appear anywhere together in column A (or is it the always the first digits after the dash)?, then you want a letter "A", "B", "C", etc as the output?

    Is it a downward consistent sequential referencing? ie. 358 = A, 357 = B, 356 = C, etc.. all the way to 342 = Q
    Last edited by NBVC; 2014-07-08 at 07:15 PM.


  3. #3
    Yes to your first question. It will be first digits after the dash.

    A 356
    B 258
    C 257
    D 355
    E 359
    F 354
    G 352
    G 351
    G 350 (is this possible to put these 3 together for G?)
    H 362
    I 369
    J 357
    K 361
    L 368
    M 360
    N 364
    O 363
    P 365
    Q 366

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I am going to assume the first 3 digits after the dash....

    First enter that last you just gave in a separater area (maybe in another tab) and you can use it to Lookup the items.

    Now, use formula:

    =INDEX(Sheet3!$A$1:$A$19,MATCH(--MID(A1,FIND("-",A1)+2,3),Sheet3!$B$1:$B$19,0))

    where Sheet3, A1:B19 contains your list of letters vs numbers and A1 in the active sheet contains first code to purge. You can copy formula down.

    If you get #N/A you have no match. If you want to hide the error with a blank or other text, then use IFERROR... e.g. =IFERROR(INDEX(Sheet3!$A$1:$A$19,MATCH(--MID(A1,FIND("-",A1)+2,3),Sheet3!$B$1:$B$19,0)),"")


  5. #5

    Attached is the spreadsheet

    worked fabulous on the first row of data, however it's not producing the correct output on the remaining rows. What to do? See attached.7414.xlsx7414.xlsx

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You put the same formula in those cells, instead of copying down. The copying down will change the A1 reference to A2, A3, etc...

    select cell F1, then double-click the little black square at bottom right corner of the cell (or drag it down to the bottom).

    Note: If there are no numbers after the dash you will get #VALUE error. The IFERROR() version I gave you will clean that up.


  7. #7
    Good afternoon,

    Drag or copy the formula down instead of using the same one.

    Hope this helps.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I think that is what I said.


  9. #9
    thank you so much! I thought I had already done that without success, but I guess I was wrong! Thanks again!

  10. #10

Posting Permissions

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