|Good try, you are close on some parts, but let's break it down and look at the details.|
Let me start by saying that what I trying to do was eliminate the hard-coded numbers (20, 31, 50, etc.) and let the formula eliminate the extraneous text by finding the colons. I was not entirely successful in doing that (the 14 is still a hard coded number) and I'll explain why later. I posted the slightly faulty formula anyway just as a learning experience.
Whatever you learn from this, you'll be able to use later and I think that is what you might enjoy that. (In fact, I learned something while doing this, so I'm glad I tried it.)
OK, Let’s start with the overall MID function and break down the arguments
MID(text, start_num, num_chars)
Text: A1 - obviously ;-)
num_chars: coming later
How that works…
FIND(find_text, within_text, [start_num])
By using a second FIND function as the [start_num] argument for the first FIND, I am telling Excel to start looking for a colon after the position of the first colon.
FIND(":",A1)+1 means return the position of the colon and then add 1, giving us a [start_num] that is one position past the first colon. This returns the position of the second colon.
e.g. In the string 12:45: the first colon is in position 3, the second is in position 6 therefore FIND(":",A1,FIND(":",A1)+1) means to find a colon in A1 but start looking at position 4, not position 1. In essence, the first FIND doesn’t even know about the first colon because it doesn’t start looking for a colon until position 4, based on the value returned by the second FIND.
Then all we do is add 2 to that "position" to find the location of the start_num for the MID function. (2 positions past the second colon.)
OK, now let’s determine how many characters the MID function should extract:
This is a quick way to find the third colon in a more direct manner than using multiple finds. Allow me to explain.
This function returns the character assigned to the ASCII number used by the function.
e.g. =CHAR(65) would place an “A” in the cell.
CHAR(1) returns a character that would normally not be be found in an Excel cell, since it is not something that can be typed in (SOH, start of heading). (More on that later)
SUBSTITUTE(text, old_text, new_text, [instance_num])
old_text : a colon
This will “substitute” an SOH character for the third colon found in A1. CHAR(1) (SOH) is a safe character to use, since the odds of it already existing in the cell is extremely slim. The SUBSTITUTE function doesn’t change A1; it just changes what A1 “looks like” to whatever function is about to reference A1.
In other words, “Find an SOH character in whatever A1 would look like if you substituted an SOH character for the third colon. ;-)
OK, so now the FIND function has found the position of the third colon. Now we subtract 14 from that to account for “Instance ID” then subtract the number of characters up to the space after the second colon, leaving us with the number of characters in the string we want to extract.
Now, as I said before, my goal was to eliminate all hard coded numbers so that the formula could be used regardless of the number of characters in the entire string or in the CIN value or anything else. I was trying to extract the string solely based on the locations of the colons. Unfortunately, by the time I got burnt out from working on this, I couldn’t come up with a way to deal with the fact that third colon comes after the Instance ID and we had to back up from there. In other words, I couldn’t figure out how to have the formula determine the length of “Instance ID” by itself. For example, as written, if “Instance ID:” suddenly becomes “Instance ID Number:” my formula will fail.
I’m sure it can be done, but as of now I don’t know how. Work on that for us, would you? ;-)
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.