Complex if and mid function.
 

I have been saving the worst question for last. So here it is…Take his
line for example:

" TBA TBA LECT TTH 01:30PM 02:50PM"

The quotes show were it starts and ends. (There is some spacing in the
beginning.) This is largely what most of my recent questions have been
regarding. I need to create a function that will produce as a solution
whatever you see after the LECT. It would range from 1 to 3 characters.
(It is basically a day of the week: M,T,W,TH or THH, and F.)

Now here is the major problem! In this example the TTH starts in the
22nd space. There are hundreds of these, but they do not necessarily
start in the 22nd slot. Coming from the right it will always start in
the 22nd, 23rd, or 24th slot.

How can I write an equation to pull out that date section. I am so
lost


PS: I have been working on this today. Here is what I have been trying
to do, but it is giving me one or two errors:

=MID(IF(ISBLANK(C2),K2,C2),FIND((IF(ISBLANK(C2),K2 ,C2)),(" * "),20),4)

In the red part I attempted to say look in the section with the text. I
think that part is okay. In the middle part (here is where the trouble
is), I attempted to get the starting point of the day text, so that it
could go into the mid function. No matter what the combination, the day
(as it occurs after LECT) will always be at least 20 spaces in. If I
could get this value, then I would be able to get the part in green
(the space plus the next 3 characters, incase of THH).

Well...goodluck. I know I can't figure this out.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382788

I think I have it!

=MID(IF(ISBLANK(C2),K2,C2),(SEARCH(" *
",(IF(ISBLANK(C2),K2,C2)),20))+1,3)

Now I just have a few issues here.

1. This searches columns C and K. Only one of them will contain data.
The other will be blank. Occasionally both will be blank. In this case
I need a blank cell to be returned. Right now I am getting a #VALUE!.
How can I fix this?

2. This next one may sound odd, but I need it for a more simple
equation. Say this solution picks up a M for Monday. Sometimes there is
a space or two before and after the M solution. I need to only have the
M. Is there like a "Nospaces" function or something I can add in here?

Thanks.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382788

I might be running out today. If I don't get back to any responses
today, I will do it as soon as possible.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382788

=if(and(isblank(c2),isblank(k2)),"",your equation)
for extra spaces try the Trim() function

"brookdale" wrote:


I think I have it!

=MID(IF(ISBLANK(C2),K2,C2),(SEARCH(" *
",(IF(ISBLANK(C2),K2,C2)),20))+1,3)

Now I just have a few issues here.

1. This searches columns C and K. Only one of them will contain data.
The other will be blank. Occasionally both will be blank. In this case
I need a blank cell to be returned. Right now I am getting a #VALUE!.
How can I fix this?

2. This next one may sound odd, but I need it for a more simple
equation. Say this solution picks up a M for Monday. Sometimes there is
a space or two before and after the M solution. I need to only have the
M. Is there like a "Nospaces" function or something I can add in here?

Thanks.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382788

I'm not entirely clear on what you're doing, but this will extract the
date section from C2:

=MID(LEFT(TRIM(C2),FIND("$",SUBSTITUTE(TRIM(C2)," ","$",4))-1),
FIND("^",SUBSTITUTE(TRIM(C2)," ","^",3))+1,255)


In article ,
brookdale
wrote:

I have been saving the worst question for last. So here it is…Take his
line for example:

" TBA TBA LECT TTH 01:30PM 02:50PM"

The quotes show were it starts and ends. (There is some spacing in the
beginning.) This is largely what most of my recent questions have been
regarding. I need to create a function that will produce as a solution
whatever you see after the LECT. It would range from 1 to 3 characters.
(It is basically a day of the week: M,T,W,TH or THH, and F.)

Now here is the major problem! In this example the TTH starts in the
22nd space. There are hundreds of these, but they do not necessarily
start in the 22nd slot. Coming from the right it will always start in
the 22nd, 23rd, or 24th slot.

How can I write an equation to pull out that date section. I am so
lost


PS: I have been working on this today. Here is what I have been trying
to do, but it is giving me one or two errors:

=MID(IF(ISBLANK(C2),K2,C2),FIND((IF(ISBLANK(C2),K2 ,C2)),(" * "),20),4)

In the red part I attempted to say look in the section with the text. I
think that part is okay. In the middle part (here is where the trouble
is), I attempted to get the starting point of the day text, so that it
could go into the mid function. No matter what the combination, the day
(as it occurs after LECT) will always be at least 20 spaces in. If I
could get this value, then I would be able to get the part in green
(the space plus the next 3 characters, incase of THH).

Well...goodluck. I know I can't figure this out.

Amazing bj! This is actaully embarrasing because I should have been able
to do the blank space one on my own. As for the TRIM, I have learned a
new function.

Thanks so much. I think that I am finally done with this paper. I have
been using these things today to do huge course rosters for the
college.

Thanks so much. Andrew.


--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php...o&userid=24617
View this thread: http://www.excelforum.com/showthread...hreadid=382788