Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|