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

brendaoc

brendaoc

New member
Joined
Jul 8, 2014
Messages
9
Reaction score
0
Points
0
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 BCol CCol dCol E
916 - 35806 35806AL741424999387/4/14$7.26
3246 - 35758 35758AL741424999667/4/14$6.50
1038 - 36830 36830AL741425001657/4/14$0.82
586 - 35976 35976AL741425001707/4/14$3.15
 
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:
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
 
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)),"")
 
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.
 
Good afternoon,

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

Hope this helps.
 
thank you so much! I thought I had already done that without success, but I guess I was wrong! Thanks again!
 
You must log in or register to reply here.
Back
Top