IF and LEN and MID Problem

[Guest]

Hi

I am trying to extract data out of a column and break it into 3 columns as
follows:

Data= N Network 01/01/05 JP JPM

=IF(LEN(A1=24),MID(A1,20,3),IF(LEN(A1=25),MID(A1,21,3),IF(LEN(A1=26),MID(A1,22,3)))) etc

Result= JP

What I require it the date, and the Initials in 3 columns without the
initial text.

It appears that the IF part of the statement doesnt work and I can't figure
out why? They work as an individual statement and excel does not throw any
errors up with the formula.

Any suggestions greatly appreciated.

Regards

John

 

[Bob Phillips]

To start, the LEN test should be of the form

LEN(A1)=24

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi

I am trying to extract data out of a column and break it into 3 columns as
follows:

Data= N Network 01/01/05 JP JPM

=IF(LEN(A1=24),MID(A1,20,3),IF(LEN(A1=25),MID(A1,21,3),IF(LEN(A1=26),MID(A1,
22,3)))) etc

 

[Guest]

Hi there,

It looks to me like you're getting this data from somewhere else and trying
to parse out some info. If that's the case, and the data in the cells you're
looking at always appears in the same format, have you thought about using
the Text to Columns function to break up the data into separate cells? That
might be quicker and easier, not to mention a whole lot less
processor-intensive.

Tom Hayakawa

 

[BobT]

You don't need all the if statements either
=MID(A1,LEN(A1)-15,8) is the date
=MID(A1,LEN(A1)-6,2) is the first set of two initials
=MID(A1,LEN(A1)-3,3) is the second set of three initials

Unless I miscounted

 

[Ken Wright]

Assuming your logic and example always hold good then the following should
do

Date
=--MID(A1,FIND("/",A1)-2,8) (Format as a date)

Initials
=MID(A1,LEN(A1)-4,3)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi

I am trying to extract data out of a column and break it into 3 columns as
follows:

Data= N Network 01/01/05 JP JPM

=IF(LEN(A1=24),MID(A1,20,3),IF(LEN(A1=25),MID(A1,21,3),IF(LEN(A1=26),MID(A1,
22,3)))) etc

 

[Guest]

Thanks Tom

I have opted for this method, only ever used this when importing from text,
it very useful though!

Thanks again

John