|
Re: Complex if and mid function.
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.1rcgfo_1119974960.0008@excelforum-nospam.com>,
brookdale <brookdale.1rcgfo_1119974960.0008@excelforum-nospam.com>
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.
|