Len Left If formula

Jack in the UK · Feb 28th, 2002, 07:22 AM

Hi Guys looking for some ideas onthis one

B1 = Due 1 March 2002
Or
B1 = Not Due

In A1 = If B1 left = DUE then A1 = Due
or if B1 Left = Not Due the A1 = Not Due

Hope thats ok guys..

cheers for you help in advance ill need to drag this one down a few billion...

Cheers

Rdgs

Steve Hartman · Feb 28th, 2002, 07:28 AM

In A1 put =IF(Left(B1,3)="Due","Due","Not Due")

or if you wanted the Due to stand out:

=IF(Left(B1,3)="Due","Due","")

[ This Message was edited by: Steve Hartman on 2002-02-28 06:36 ]

Derek · Feb 28th, 2002, 07:32 AM

Hi
How about

=IF(LEFT(B1,1)="D","Due",IF(LEFT(B1,1)="N","Not Due",""))

Assumes you don't have anything else in column B starting with D or N

Regards
Derek

Aladin Akyurek · Feb 28th, 2002, 07:56 AM

Jack,

=IF(LEFT(B1,4)="Due ","Due","Not Due")

where the structure in B is assumed to be:

"Due" followed by a-space followed by a-date. When that's the case, we get "Due" in A,otherwise "Not Due".

Aladin

Jack in the UK · Feb 28th, 2002, 08:00 AM

Cheers guys, im some ideas to work on,

Speak soon

Jack

IML · Feb 28th, 2002, 08:27 AM

Just one more for true overkill:
=IF(ISNUMBER(SEARCH("not due",B1)),"not due",IF(ISNUMBER(SEARCH("due",B1)),"due",""))

may help if you have some sloppy typest (like me) or those who don't follow your suggested format

[ This Message was edited by: IML on 2002-02-28 07:28 ]

Feb 28th, 2002, 07:22 AM	#1
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Hi Guys looking for some ideas onthis one B1 = Due 1 March 2002 Or B1 = Not Due In A1 = If B1 left = DUE then A1 = Due or if B1 Left = Not Due the A1 = Not Due Hope thats ok guys.. cheers for you help in advance ill need to drag this one down a few billion... Cheers Rdgs __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Feb 28th, 2002, 08:00 AM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Cheers guys, im some ideas to work on, Speak soon Jack __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 07:28 AM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	In A1 put =IF(Left(B1,3)="Due","Due","Not Due") or if you wanted the Due to stand out: =IF(Left(B1,3)="Due","Due","") [ This Message was edited by: Steve Hartman on 2002-02-28 06:36 ]

Feb 28th, 2002, 07:32 AM	#3
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi How about =IF(LEFT(B1,1)="D","Due",IF(LEFT(B1,1)="N","Not Due","")) Assumes you don't have anything else in column B starting with D or N Regards Derek

Feb 28th, 2002, 07:56 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,314	Jack, =IF(LEFT(B1,4)="Due ","Due","Not Due") where the structure in B is assumed to be: "Due" followed by a-space followed by a-date. When that's the case, we get "Due" in A,otherwise "Not Due". Aladin

Feb 28th, 2002, 08:27 AM	#6
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Just one more for true overkill: =IF(ISNUMBER(SEARCH("not due",B1)),"not due",IF(ISNUMBER(SEARCH("due",B1)),"due","")) may help if you have some sloppy typest (like me) or those who don't follow your suggested format [ This Message was edited by: IML on 2002-02-28 07:28 ]