CODEWITHSUNDEEP
excelexcel-formula
Cleaver
Cleaver

Reputation: 67

Replacing Letters with Numbers in sequence in an excel formula

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.

enter image description here

Any help will be appreciated, cheers

Upvotes: 0

Views: 1379

Answers (2)

Solar Mike
Solar Mike

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.

enter image description here

Upvotes: 0

Axel Richter
Axel Richter

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:

enter image description here

Formula in R3 downwards:

=LEFT(P3,7)&CODE(MID(P3,8,1))-65&RIGHT(P3,2)

Upvotes: 1

Related Questions