If Statement: “Starts With”

**succel** · 08-03-2010, 04:01 PM

I’m having trouble writing an if statement that tests whether or not a cell starts with a certain value. Here’s the outcome I’m trying to produce:

If A1 starts with 1/, 2/, or 3/, then insert “Q1”
If A1 starts with 4/, 5/, or 6/, then insert “Q2”
If A1 starts with 7/, 8/, or 9/ then insert “Q3”
If A1 starts with 10/, 11/, or 12/, then insert “Q4”

How do I make one if statement that combines these stipulations?

**daddylonglegs** · 08-03-2010, 04:21 PM

Is A1 a date? (not a text representation of a date). If so then you could use a formula like this

="Q"&MATCH(MONTH(A1),{1,4,7,10})

**martindwilson** · 08-03-2010, 04:24 PM

if its text
=CHOOSE(CEILING(LEFT(A1,(FIND("/",A1)-1))/3,1),"Q1","Q2","Q3","Q4")
or just
="Q"&CHOOSE(CEILING(LEFT(A1,(FIND("/",A1)-1))/3,1),1,2,3,4)

**succel** · 08-03-2010, 04:26 PM

Hi dll,

It is indeed, and your formula works perfectly!! Thanks so much for your quick and accurate help

**succel** · 08-03-2010, 04:29 PM

martin, yours also works flawlessly. thanks, guys!

**succel** · 08-11-2010, 01:47 PM

If I want it to display the year as well (such as 2010 Q3), how would I modify the code?

**shg** · 08-11-2010, 02:00 PM

=year(a1) & " q" & match(month(a1),{1,4,7,10})

**succel** · 08-11-2010, 02:24 PM

shg, that works :D

If Statement: “Starts With”

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Re: If Statement: “Starts With”

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions