Forum Discussion
Is there a way to get a cell to show alternate text based off deliberate characters found in a cell?
Hello, Is there a formula that will either place "Discount" or "Received" in a cell if another cell contains the characters "%" or "-" in it respectively.
I have enclosed a image to better demonstrate below. The idea is if I place 12% in col. A it will find the percentage character then in col. C will show "Discount" but if it is a date with a dash then it shows "Received". If it finds neither I'd like it to be blank. The cells in column A are formatted as TEXT.
Any assistance will be greatly appreciated.
- try
=IFS(ISNUMBER(SEARCH("%",A1)),"Discount",ISNUMBER(SEARCH("-",A1)),"Received",1,"")
if you have an older version of excel and IFS() doesn't work then:
=IF(ISNUMBER(SEARCH("%",A1)),"Discount",IF(ISNUMBER(SEARCH("-",A1)),"Received",""))
- try
=IFS(ISNUMBER(SEARCH("%",A1)),"Discount",ISNUMBER(SEARCH("-",A1)),"Received",1,"")
if you have an older version of excel and IFS() doesn't work then:
=IF(ISNUMBER(SEARCH("%",A1)),"Discount",IF(ISNUMBER(SEARCH("-",A1)),"Received",""))- Thank you this worked perfectly! I don't know how you all know which formulas to use, but I am so very grateful that you do and for the help. Cheers
In C1:
=LET(f, LEFT(CELL("format", A1)), SWITCH(f, "P", "Discount", "D", "Received", ""))
Fill down.
- Thank you for this, it is brilliant. It took me a while to figure out that this works without the need to format the A column as TEXT. Does it recognizes the format and places the text accordingly, that is P for Percentage and D for Date?
Yes, indeed - see the Format codes section in CELL function
