Solved

Changing value in one cell based on last part of text string in another cell

Posted on 2014-03-15
2
374 Views
Last Modified: 2014-03-15
Column L contains text strings of various lengths. Column S has data validation that includes the following words in the dropdown menu: NEW, REVISED, DELETED.

I want to create a formula that changes the value in Column S if specific criteria are met in Column L as follows:

If the text string in column L ends with "- NEW", then the cell in Column S (in same row) autopopulates with the word "NEW".

If text string in column L does not end with "- NEW",  then the cell in Column S (in same row) autopopulates with the word "REVISED"

If cell in column L is blank, then the cell in Column S (in same row) remains blank.

Thanks!
Andrea
0
Comment
Question by:Andreamary
2 Comments
 
LVL 13

Accepted Solution

by:
Santosh Gupta earned 500 total points
ID: 39931743
Pls try below,


=IF(L1="","",IF(RIGHT(L1,3)="NEW","NEW","REVISED"))
0
 

Author Closing Comment

by:Andreamary
ID: 39931765
Perfect...and thanks for answering so quickly!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now