Reputation: 67
I'm wanting to update a column based on another that has drill holes listed in sequence, the update needs to replace a character within the sequence, such that A = 0, B = 1, C = 2 etc... Is there a way to do this without writing a really big IF/Then type formula?
I've tried the replace formula, but I've only managed to update one letter at a time..
In the example below; The Hole_ID_2 (column R) is the desired output using the Hole_ID (Column P) data.
Any help will be appreciated, cheers
Upvotes: 0
Views: 1379
Reputation: 8415
Have you considered the substitute() function?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"a",1),"b",2),"c",3)
assuming your data starts in cell A3.
Upvotes: 0
Reputation: 61995
As long as all IDs to replace are of the same structure (10 characters in length and the letter to replace at position 8), one could get the ANSI
code of the letter using CODE
and calculate that code to the appropriate number then.
That would avoid many nested SUBSTITUTE
functions.
Your Example:
Formula in R3
downwards:
=LEFT(P3,7)&CODE(MID(P3,8,1))-65&RIGHT(P3,2)
Upvotes: 1