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 )

Cheers
Linds

 

[JulieD]

Hi Linds

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

 

[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

 

[Bernard Liengme]

Do we need the TRUE?

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

 

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

One way:

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

 

[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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

why 6 spaces?

=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