find and concat text

[joharmenezes]

Hi,


I'll get down to my query straight away:


A1 to A5 contains values, aaa,bbb, ccc,ddd, eee. I want a formula in cells B1 to B5 which return aaa1 if aaa is present, bbb2 if bbb is present and so on... I tried using FIND and SEARCH but since they return the #VALUE error when a string is not present I couldn't go far.


Just want to add that cells A1 to A5 may not necessarily contain exact values like aaa, bbb, etc but may contain values which include them. For example xaaacd, drbbbfgjg, asdfccc, etc snd then the values returned should be xaaacd1, drbbbfgjg2, asdfccc3


Thanks

 

[mercatog]

Hi T100

Try this in B1

=IF(ISNUMBER(FIND("aaa",A1)),"aaa1","")

Regards

 

[joharmenezes]

wow thanks mercatog that was fast. I should have used ISNUMBER

 

[mercatog]

Note that you can also use

=IF(ISERROR(FIND("aaa",A1)),"","aaa1")