I need a formula to replace certain value in the cell by comparing the entire 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 value.

Mike TV

@Nishu

This should work. Example sheet:

Formula:

=IF(CONTAINS("TP", Source@row), Source@row, IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jan", Source@row)), REPLACE(Source@row, 7, 1, "1"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Feb", Source@row)), REPLACE(Source@row, 7, 1, "2"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Mar", Source@row)), REPLACE(Source@row, 7, 1, "3"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Apr", Source@row)), REPLACE(Source@row, 7, 1, "4"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("May", Source@row)), REPLACE(Source@row, 7, 1, "5"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jun", Source@row)), REPLACE(Source@row, 7, 1, "6"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jul", Source@row)), REPLACE(Source@row, 7, 1, "7"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Aug", Source@row)), REPLACE(Source@row, 7, 1, "8"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Sept", Source@row)), REPLACE(Source@row, 7, 1, "9"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Oct", Source@row)), REPLACE(Source@row, 7, 1, "10"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Nov", Source@row)), REPLACE(Source@row, 7, 1, "11"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Dec", Source@row)), REPLACE(Source@row, 7, 1, "12"), "")))))))))))))

Mike TV

@Nishu

This should work. Example sheet:

Formula:

=IF(CONTAINS("TP", Source@row), Source@row, IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jan", Source@row)), REPLACE(Source@row, 7, 1, "1"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Feb", Source@row)), REPLACE(Source@row, 7, 1, "2"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Mar", Source@row)), REPLACE(Source@row, 7, 1, "3"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Apr", Source@row)), REPLACE(Source@row, 7, 1, "4"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("May", Source@row)), REPLACE(Source@row, 7, 1, "5"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jun", Source@row)), REPLACE(Source@row, 7, 1, "6"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Jul", Source@row)), REPLACE(Source@row, 7, 1, "7"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Aug", Source@row)), REPLACE(Source@row, 7, 1, "8"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Sept", Source@row)), REPLACE(Source@row, 7, 1, "9"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Oct", Source@row)), REPLACE(Source@row, 7, 1, "10"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Nov", Source@row)), REPLACE(Source@row, 7, 1, "11"), IF(AND(CONTAINS("AP", Source@row), CONTAINS("Dec", Source@row)), REPLACE(Source@row, 7, 1, "12"), "")))))))))))))

Nishu

Thanks for your response Matt.