CODEWITHSUNDEEP
excelautomationexcel-formulaspreadsheet
Mike
Mike

Reputation: 19

Excel substitute a cell and add add a letter/number

I am trying to create a formula that can do three things within the same cell. The formula needs to get a value from another cell, but only the middle part (which can be solved with the substitute function). Next, it needs to add a letter at the start, and another number at the end.

Example:

I have the following three values:

  1. 201642A
  2. 201142A
  3. 201342A

The values need to be converted to the following

  1. A16421
  2. A11421
  3. A13421

As you can see, the first two numbers, "20" have been replaced with "A", and the "A" at the end has been replaced with "1".

Reason: I need to do this for hundreds of records across multiple columns so a bit of automation would save me alot of time.

Here is an example in Excel.

Upvotes: 0

Views: 185

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

try

=if(left(A1,2)=20,"A") & mid(A1,3,4) & if(right(a1,1)="A",1)

Upvotes: 1

Related Questions