CODEWITHSUNDEEP
excelstringif-statementreplaceexcel-formula
Andre_k
Andre_k

Reputation: 1728

Replace for same consequently occurring word in excel

I have a excel column called as Add_type which has type of address

Add_type

    route
    Sub-locality
    locality
    administrative_area_level_1
    country
    postal_code
    premise
    Sub-locality
    Sub-locality
    administrative_area_level_1
    country
    Sub-locality
    Sub-locality
    Sub-locality

So What I expect is if there is occurrence of word " Sub-locality" consecutively then second occurrence should be replaced by Sub-locality1 AND if there are three consecutive occurrences of " Sub-locality" the second should be replaced by "Sub-locality1" and third by "Sub-locality2" I tried with =IF(A8=A9,"Sub-locality1",A8) but this doesn't solve the problem

Such that my output would look like

    route
    Sub-locality
    locality
    administrative_area_level_1
    country
    postal_code
    premise
    Sub-locality
    Sub-locality1
    administrative_area_level_1
    country
    Sub-locality
    Sub-locality1
    Sub-locality2

Any suggestion on how this should be done. Any help would be much appreciated. Thanks.

Upvotes: 0

Views: 27

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

This one should work as long as there is no more than 10 repetitions.

=IF(A8=A9,A9 & IF(A8=B8,1,RIGHT(B8)+1),A8)

Insert this in B9.

Upvotes: 1

Related Questions