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

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

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. 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

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. 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. ## 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. 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. Good afternoon,

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

Hope this helps.

8. I think that is what I said.

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

10. It is

#### Posting Permissions

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