IF OR Formula

[Lindsey M]

Hi everyone, hope you are all well

I'm having a slight problem with a formula that I've written

=IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))

This would be placed in A3 then copied down all cells in the A column, the
following is an example of what would be in the other cells (just so you get
an idea what i'm trying to do)

B2 = Joe Bloggs
B3 = 381034 Mary Smith
B4 = 345876 John Smith
B5 = |spaces| Lunch 12:00 13:00
B6 = |spaces| Lunch 12:30 13:30

and so on.

The prob I'm having is that the above formula works exactly as I want it to,
well, that is if the A3 will be either Joe Bloggs if B2 is not blank and it
will be blank if the first 6 chars are numbers, but I need to incorporate
that it should also go blank if the first 6 chars are empty (in the case of
B5 and B6), instead it is copying these across.

I'm thinking that I should use the OR function but everything i've tried is
not working.

Has anyone got any ideas on this or can you point me in the right direction?
Any help will be gratefully received :o)

Cheers
Linds

[JulieD]

Hi Linds

try
=IF(B2="","",IF(OR(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,LEFT(B3,6)="
"),"",B2))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Lindsey M" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, hope you are all well
>
> I'm having a slight problem with a formula that I've written
>
> =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> This would be placed in A3 then copied down all cells in the A column, the
> following is an example of what would be in the other cells (just so you
> get
> an idea what i'm trying to do)
>
> B2 = Joe Bloggs
> B3 = 381034 Mary Smith
> B4 = 345876 John Smith
> B5 = |spaces| Lunch 12:00 13:00
> B6 = |spaces| Lunch 12:30 13:30
>
> and so on.
>
> The prob I'm having is that the above formula works exactly as I want it
> to,
> well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
> it
> will be blank if the first 6 chars are numbers, but I need to incorporate
> that it should also go blank if the first 6 chars are empty (in the case
> of
> B5 and B6), instead it is copying these across.
>
> I'm thinking that I should use the OR function but everything i've tried
> is
> not working.
>
> Has anyone got any ideas on this or can you point me in the right
> direction?
> Any help will be gratefully received :o)
>
> Cheers
> Linds
>
>

[CLR]

=IF(OR(B2="",B2=" "),"",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))

Note, 6 spaces between the doublequotes in the OR section

Vaya con Dios,
Chuck, CABGx3


"Lindsey M" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, hope you are all well
>
> I'm having a slight problem with a formula that I've written
>
> =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> This would be placed in A3 then copied down all cells in the A column, the
> following is an example of what would be in the other cells (just so you
get
> an idea what i'm trying to do)
>
> B2 = Joe Bloggs
> B3 = 381034 Mary Smith
> B4 = 345876 John Smith
> B5 = |spaces| Lunch 12:00 13:00
> B6 = |spaces| Lunch 12:30 13:30
>
> and so on.
>
> The prob I'm having is that the above formula works exactly as I want it
to,
> well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
it
> will be blank if the first 6 chars are numbers, but I need to incorporate
> that it should also go blank if the first 6 chars are empty (in the case
of
> B5 and B6), instead it is copying these across.
>
> I'm thinking that I should use the OR function but everything i've tried
is
> not working.
>
> Has anyone got any ideas on this or can you point me in the right
direction?
> Any help will be gratefully received :o)
>
> Cheers
> Linds
>
>

[Bernard Liengme]

Do we need the TRUE?

=IF(OR(B2="",B2=" "),"",IF(ISNUMBER(VALUE(LEFT(B2,6))),"",B2))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

[CLR]

"OR", a little shorter version............

=IF(OR(B2="",B2=" ",ISNUMBER(LEFT(B2,6)*1)),"",B2)

Vaya con Dios,
Chuck, CABGx3


"Bernard Liengme" <[email protected]> wrote in message
news:#[email protected]...
> Do we need the TRUE?
>
> =IF(OR(B2="",B2=" "),"",IF(ISNUMBER(VALUE(LEFT(B2,6))),"",B2))
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
>

[JE McGimpsey]

One way:

=IF(OR(LEN(TRIM(LEFT(B2,6)))=0,ISNUMBER(-LEFT(B2,6))),"",B2)


In article <[email protected]>,
"Lindsey M" <[email protected]> wrote:

> Hi everyone, hope you are all well
>
> I'm having a slight problem with a formula that I've written
>
> =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> This would be placed in A3 then copied down all cells in the A column, the
> following is an example of what would be in the other cells (just so you get
> an idea what i'm trying to do)
>
> B2 = Joe Bloggs
> B3 = 381034 Mary Smith
> B4 = 345876 John Smith
> B5 = |spaces| Lunch 12:00 13:00
> B6 = |spaces| Lunch 12:30 13:30
>
> and so on.
>
> The prob I'm having is that the above formula works exactly as I want it to,
> well, that is if the A3 will be either Joe Bloggs if B2 is not blank and it
> will be blank if the first 6 chars are numbers, but I need to incorporate
> that it should also go blank if the first 6 chars are empty (in the case of
> B5 and B6), instead it is copying these across.
>
> I'm thinking that I should use the OR function but everything i've tried is
> not working.
>
> Has anyone got any ideas on this or can you point me in the right direction?
> Any help will be gratefully received :o)
>
> Cheers
> Linds

[Lindsey M]

Hi everyone,

Thanks for all your help, I figured it out by using the following:

=IF(OR(B2="",(LEFT(B2)=" ")),"",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))

Wouldn't have got there tho without all your input so thanks again!

Cheers

Linds


"Lindsey M" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, hope you are all well
>
> I'm having a slight problem with a formula that I've written
>
> =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> This would be placed in A3 then copied down all cells in the A column, the
> following is an example of what would be in the other cells (just so you
get
> an idea what i'm trying to do)
>
> B2 = Joe Bloggs
> B3 = 381034 Mary Smith
> B4 = 345876 John Smith
> B5 = |spaces| Lunch 12:00 13:00
> B6 = |spaces| Lunch 12:30 13:30
>
> and so on.
>
> The prob I'm having is that the above formula works exactly as I want it
to,
> well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
it
> will be blank if the first 6 chars are numbers, but I need to incorporate
> that it should also go blank if the first 6 chars are empty (in the case
of
> B5 and B6), instead it is copying these across.
>
> I'm thinking that I should use the OR function but everything i've tried
is
> not working.
>
> Has anyone got any ideas on this or can you point me in the right
direction?
> Any help will be gratefully received :o)
>
> Cheers
> Linds
>
>

[CLR]

As long as you got what you needed, that's what counts............thanks for
the feedback

Vaya con Dios,
Chuck, CABGx3


"Lindsey M" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> Thanks for all your help, I figured it out by using the following:
>
> =IF(OR(B2="",(LEFT(B2)="
")),"",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> Wouldn't have got there tho without all your input so thanks again!
>
> Cheers
>
> Linds
>
>
> "Lindsey M" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone, hope you are all well
> >
> > I'm having a slight problem with a formula that I've written
> >
> > =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
> >
> > This would be placed in A3 then copied down all cells in the A column,
the
> > following is an example of what would be in the other cells (just so you
> get
> > an idea what i'm trying to do)
> >
> > B2 = Joe Bloggs
> > B3 = 381034 Mary Smith
> > B4 = 345876 John Smith
> > B5 = |spaces| Lunch 12:00 13:00
> > B6 = |spaces| Lunch 12:30 13:30
> >
> > and so on.
> >
> > The prob I'm having is that the above formula works exactly as I want it
> to,
> > well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
> it
> > will be blank if the first 6 chars are numbers, but I need to
incorporate
> > that it should also go blank if the first 6 chars are empty (in the case
> of
> > B5 and B6), instead it is copying these across.
> >
> > I'm thinking that I should use the OR function but everything i've tried
> is
> > not working.
> >
> > Has anyone got any ideas on this or can you point me in the right
> direction?
> > Any help will be gratefully received :o)
> >
> > Cheers
> > Linds
> >
> >
>
>

why 6 spaces?

"CLR" <[email protected]> wrote in message
news:[email protected]...
> =IF(OR(B2="",B2=" "),"",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
>
> Note, 6 spaces between the doublequotes in the OR section
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Lindsey M" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone, hope you are all well
> >
> > I'm having a slight problem with a formula that I've written
> >
> > =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
> >
> > This would be placed in A3 then copied down all cells in the A column,
the
> > following is an example of what would be in the other cells (just so you
> get
> > an idea what i'm trying to do)
> >
> > B2 = Joe Bloggs
> > B3 = 381034 Mary Smith
> > B4 = 345876 John Smith
> > B5 = |spaces| Lunch 12:00 13:00
> > B6 = |spaces| Lunch 12:30 13:30
> >
> > and so on.
> >
> > The prob I'm having is that the above formula works exactly as I want it
> to,
> > well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
> it
> > will be blank if the first 6 chars are numbers, but I need to
incorporate
> > that it should also go blank if the first 6 chars are empty (in the case
> of
> > B5 and B6), instead it is copying these across.
> >
> > I'm thinking that I should use the OR function but everything i've tried
> is
> > not working.
> >
> > Has anyone got any ideas on this or can you point me in the right
> direction?
> > Any help will be gratefully received :o)
> >
> > Cheers
> > Linds
> >
> >
>
>

[CLR]

One of the OP's requirements......

> > that it should also go blank if the first 6 chars are empty (in the case
> of


Vaya con Dios,
Chuck, CABGx3



"[email protected]" wrote:

> why 6 spaces?
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > =IF(OR(B2="",B2=" "),"",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
> >
> > Note, 6 spaces between the doublequotes in the OR section
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Lindsey M" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi everyone, hope you are all well
> > >
> > > I'm having a slight problem with a formula that I've written
> > >
> > > =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2))
> > >
> > > This would be placed in A3 then copied down all cells in the A column,
> the
> > > following is an example of what would be in the other cells (just so you
> > get
> > > an idea what i'm trying to do)
> > >
> > > B2 = Joe Bloggs
> > > B3 = 381034 Mary Smith
> > > B4 = 345876 John Smith
> > > B5 = |spaces| Lunch 12:00 13:00
> > > B6 = |spaces| Lunch 12:30 13:30
> > >
> > > and so on.
> > >
> > > The prob I'm having is that the above formula works exactly as I want it
> > to,
> > > well, that is if the A3 will be either Joe Bloggs if B2 is not blank and
> > it
> > > will be blank if the first 6 chars are numbers, but I need to
> incorporate
> > > that it should also go blank if the first 6 chars are empty (in the case
> > of
> > > B5 and B6), instead it is copying these across.
> > >
> > > I'm thinking that I should use the OR function but everything i've tried
> > is
> > > not working.
> > >
> > > Has anyone got any ideas on this or can you point me in the right
> > direction?
> > > Any help will be gratefully received :o)
> > >
> > > Cheers
> > > Linds
> > >
> > >
> >
> >
>
>
>