ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   IF and LEN and MID Problem (https://www.excelbanter.com/excel-worksheet-functions/15045-if-len-mid-problem.html)

JonnieP

IF and LEN and MID Problem
 
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

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)


"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




Tom Hayakawa

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


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

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



.


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

"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




JonnieP

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



All times are GMT +1. The time now is 11:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com