Thread: 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.

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

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

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

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

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

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