Formula to replace a certain part of cell value.

Nishu

I have cell values in Column 1. I need those values in Column 2 by replacing certain part of number value for the cell which is having AP in the cell value.

AP 22 or 23 or 24 should remain same as AP 22 or 23 or 24 only 02 has to be replaced with 07 if it is having Jul month in cell value.02 has to be replaced with 04 if it is having Apr month in cell value.03 has to be replaced with 08 if it is having Aug month in cell value.

However, if cell vale is having TP then same value needs to be displayed in column 2.

For ex:

1. AP 2202 Jul needs to be replaced with AP 2207  Jul. However if it is TP 2202 Jul, the same value should be retained with TP 2202 Jul nothing should be replaced.
2. Either if it's AP 2202 Jul or AP2302 Jul or AP 2402 Jul, replaced value should be AP 2207 Jul or AP2307 Jul or AP 2407 Jul.

---- AP 22 or 23 or 24 should remain same as AP 22 or 23 or 24 only 02 has to be replaced with 07.

3. AP 2303 Aug Test needs to be replaced by AP 2308 Aug Test.

Basically, cell value having AP needs to replace the 7th character/number place and other than AP such as TP it should retain the same valu

Genevieve P.

Hi @Nishu

Will your values always have the same structure?

XX YYYY XXX...?

We can first use a CONTAINS function to see if the cell Contains "AP" and "Jul", then we can use the SUBSTITUTE function to change 2 to be 7:

=IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Jul", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "07")

We'll need to build out instructions for each of your possible adjustments, so if you only have the 3 (Jul, Apr, and Aug), then this is what it could look like:

=IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Jul", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "07"), IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Apr", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "04"), IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Aug", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "08"), [Column 1]@row)))

Cheers,

Genevieve

Genevieve P.

Hi @Nishu

Will your values always have the same structure?

XX YYYY XXX...?

We can first use a CONTAINS function to see if the cell Contains "AP" and "Jul", then we can use the SUBSTITUTE function to change 2 to be 7:

=IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Jul", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "07")

We'll need to build out instructions for each of your possible adjustments, so if you only have the 3 (Jul, Apr, and Aug), then this is what it could look like:

=IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Jul", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "07"), IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Apr", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "04"), IF(AND(CONTAINS("AP", [Column 1]@row), CONTAINS("Aug", [Column 1]@row)), SUBSTITUTE([Column 1]@row, "02", "08"), [Column 1]@row)))

Cheers,

Genevieve

Nishu

Thank you very much Genevieve.