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 demon...
- 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 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
- Oooh, that is so cool!
Thanks for following up and thanks for the link! It is very helpful.
You all are amazing! I cannot express it enough.
cheers!- My apologies for pressing on this, but I have been revisiting some choices I made in my spreadsheet since you presented this formula. Though I read through the link you provided, I still can't figure out a change I'd like to make.
If I wanted C1 to now show what is in A1 with the same format, how would I adjust this formula to do so please? It will follow the same premise of it is a percentage or date, and respectfully appear with A1 content and format?
If this should be presented as a separate post, my apologies.- funny I was gonig to ask why you had column A formatted as text.
you can't use formulas to set the format but using General format Excel does pretty good at guessing. Alternatively you could use Conditional formatting to apply particular formatting based on certain rules.
that said I'm really not clear on what you are trying to do now. Instead of the words Discount and Received you just want to repeat the values in column A?
