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,2 1,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

To start, the LEN test should be of the form

LEN(A1)=24

--

HTH

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


"JonnieP" wrote in message
...
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,2 1,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

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

"JonnieP" wrote:

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,2 1,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

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

-----Original Message-----
To start, the LEN test should be of the form

LEN(A1)=24

--

HTH

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


"JonnieP" wrote in
message
...
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


.

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 :-)
----------------------------------------------------------------------------

"JonnieP" wrote in message
...
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,2 1,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

Thanks Tom

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

Thanks again

John

"Tom Hayakawa" wrote:

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

"JonnieP" wrote:

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,2 1,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