Avatar of ITSupportLCBT
ITSupportLCBT
Flag for United Kingdom of Great Britain and Northern Ireland asked on

excel formula with conditions

I'm working on an excel spread sheet with three columns. Both column A and column B contain some data. Column C is empty. I need to populate column C based on the content of columns A and B. Specifically:

If  the string 'ABC' exits s anywhere in  the contents of cell A1, then I need the content of cell C1 to be whatever the content of B1 is but pre-fixed with 'UK-'.  If 'ABC' does not exist in A1, then C1 should remain blank.

For example if the content of A1 is 'test-ABC' and the content of  B1 is 'cat' then C1 should become 'UK-cat'.


What is the formula that will allow me to do this please?
Microsoft Excel

Avatar of undefined
Last Comment
yogi4life

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
yogi4life

Your formula which you can copy without changing it into all cells in column C is:

=IF(ISNA(VLOOKUP("*"&$G$1&"*";A1:A1;1;));"";"UK-"&B1)

Additionally I have attached a spreadsheet showing it....
Formula.xls
yogi4life

ha ha ... I was 27 seconds too late ... shouldnt have attached the spreadsheet .... :-))))
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck