If Formula

[kbendelac]

I am looking to create a formula that would remove the first 2 digits of a series and if a text string contains the word Anjou replace with 78 or Moncton replace with 83.

As an example:
Cell A2 contains 704705041 and B2 will need to read 784705041/834705041 where 70 (first 2 digits of A2) will be replaced with either 78 for Anjou or 83 for Moncton in B2

 

[arun_eagertolearn]

Please try
=IF(ISNUMBER(SEARCH("Anjou",D2)),REPLACE(A2,1,2,78),IF(ISNUMBER(SEARCH("Moncton",D2)),REPLACE(A2,1,2,83),A2))

 

[Fluff]

Another option

=REPLACE(A2,1,2,IF(ISNUMBER(SEARCH("Anjou",D2)),78,IF(ISNUMBER(SEARCH("Moncton",D2)),83,"")))

 

[kbendelac]

Thank you both! This works perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[arun_eagertolearn]

Thanks for the feedback

 

[Scott Huish]

Another option

=REPLACE(A2,1,2,IF(ISNUMBER(SEARCH("Anjou",D2)),78,IF(ISNUMBER(SEARCH("Moncton",D2)),83,"")))

I think this will chop off the first 2 characters if neither text is found.