Tidy split of a column

[carol]

Hi,
I have the FIND, then LEFT and MID working well.
Unfortunatly the column I refer to has a mixture of
single letter references and then what I want to split
CRW,15 or BAW,35.

With the single letter references (X,L,R,E,A) I get the
error #VALUE in the FIND, LEFT and MID columns.

How can I make it that they are blank ??

=IF(F="X"," ",FIND(",",F6)) only gives me the #NAME
error!!!

Any assistance would be greatly appreciated.
Cheers
Carol

 

[Bob Phillips]

=IF(NOT(ISNUMBER(FIND(",",F6))),"",IF(F="X"," ",FIND(",",F6)) )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,
Excellent quick reply .thanks.
I tired that and all they single reference files are
blank - but now with the CRW,15 or BA,5 references in the
column - I get the #NAME error
Carol

 

[Guest]

Hi Bob,
Excellent quick reply .thanks.
I tired that and all they single reference files are
blank - but now with the CRW,15 or BA,5 references in the
column - I get the #NAME error
Carol

 

[Bob Phillips]

Carol,

That is because of your

IF(F="X"," ",...

F is what is giving the name error. I got it also, but assumed it was
worikbook name you were using. Presumably it should refer to a cell, F1?
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)