| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding text in a cell and returning a value based on that text
I'm trying to create a formula that will look at the cell containing
the text "B46292P" and if it finds the letter "P", return the word "Paperback". Can anyone help me? If it helps, the P would always be in the last position in the cell. However, sometimes, I would need to find letters that would be in the last 2 positions in the cell to return a value. Thanks so much for any help!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding text in a cell and returning a value based on that text
=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"P",""))0,"Paperback","")
Sitaram |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding text in a cell and returning a value based on that text
If you want it regardless of where the P is
=IF(FIND("p",A1)<"","Paperback","") -- -- -John Please rate when your question is answered to help us and others know what is helpful. wrote in message oups.com... I'm trying to create a formula that will look at the cell containing the text "B46292P" and if it finds the letter "P", return the word "Paperback". Can anyone help me? If it helps, the P would always be in the last position in the cell. However, sometimes, I would need to find letters that would be in the last 2 positions in the cell to return a value. Thanks so much for any help!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding text in a cell and returning a value based on that text
Thank you so much! This worked perfectly!
Lori P Sitaram wrote: =IF((LEN(A1)-LEN(SUBSTITUTE(A1,"P",""))0,"Paperback","") Sitaram |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding text in a cell and returning a value based on that text
One mo
=if(countif(a1,"*p*")0,"Paperback","not-paperback") " wrote: I'm trying to create a formula that will look at the cell containing the text "B46292P" and if it finds the letter "P", return the word "Paperback". Can anyone help me? If it helps, the P would always be in the last position in the cell. However, sometimes, I would need to find letters that would be in the last 2 positions in the cell to return a value. Thanks so much for any help!! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding text in a cell and returning a value based on that text
Just a note to the OP:
This will actually search for a lower case "p". Change Find to Search if you want to find either P or p. Or just make that "p" into a "P" if you want to match uppercase P. ps. The =countif() suggestion is not case sensitive, either. John Bundy wrote: If you want it regardless of where the P is =IF(FIND("p",A1)<"","Paperback","") -- -- -John Please rate when your question is answered to help us and others know what is helpful. wrote in message oups.com... I'm trying to create a formula that will look at the cell containing the text "B46292P" and if it finds the letter "P", return the word "Paperback". Can anyone help me? If it helps, the P would always be in the last position in the cell. However, sometimes, I would need to find letters that would be in the last 2 positions in the cell to return a value. Thanks so much for any help!! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding cell value in another column and returning a true or fals | Excel Worksheet Functions | |||
| returning either a shaded cell &/or a text | Excel Discussion (Misc queries) | |||
| Returning Cell Value if someone deletes the contents of a cell | Excel Worksheet Functions | |||
| Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions | |||
| returning a text cell based on a number cell | Excel Worksheet Functions | |||
Linear Mode
