Jason sends in our question today. He has a single column of cells that contain 5 bits of information in each cell. There arent any good delimiters and the fields are not fixed width. To extract the necessary information from the cell, Episode 479 shows how to combine MID, FIND, and FIND to locate the important data.
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy.
This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy.
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, we're back in the office today.
I want to thank all of my friends and MVPs who provided podcast over the last ten days.
I hope you found that enjoyable.
Also, you know when I went to the sci-young thing on Monday, for opening day of Baseball.
Didn't realize that it had been raining there.
I threw the microphone down in the ground and of course it completely trashed the microphone.
So sorry for all the static, on Monday have a brand new mic.
Here today that hopefully will work.
Although, I have to wear this stupid mic but anyway.
We're back to questions today I have a lot of questions that came in over the last couple of weeks.
If you have a question please feel free to get it to us either leave us a voicemail or just drop me an email.
We'll get to it.
Today's question comes from Jason.
In the UK, Jason's getting stock quotes and the data comes in to column A and unfortunately, It has six bits of information right in that one cell and Jason just wants to grab the price out of there.
So, he wrote this formula he thought would work pretty well, the mid of A2 starting at position 13 for a length of seven and sure enough that does grab the 550.
But then Jason found that, there were problems if the quotes came in the morning.
So, let's Say 11:35 a.m.
That time took up one extra character and so the formula didn't work or if there were other securities where the price was only two digits instead of three digits then it's not working either.
Now, the solution to this unfortunately is rather complicated.
The first thing I did was, I took a look through that data and I said okay! Right before every price we have a colon. (:) So, if i could find the colon and then start to navigate from there.
That would be the first step.
Also, right after every price, we have a lowercase 'p' and so I used the FIND function and my first find function, when it initially worked I said I want to find a colon within cell A2 and I was surprised that It gave me a character position like 4 or 5.
Because what it was doing was finding the colon in the time and so I had to go to the third argument of the find function.
This says, hey! Start at position 7 that way I know that I'm beyond the time but basically it allows the find function to find the colon.
That happens after position 7 and it says that it happens in character 12 or 13 or 12 in row 4.
Basically, allows us to kind of hone in, on where the price is going to start.
It's gonna start a character after that position.
I thought I was gonna be in trouble, when I was finding the p because It might find the capital P.
and P M but It turns out that the find function is case-sensitive.
So, finding in quotes the lowercase p with an A2, will tell me that that p occurs in character position 20.
Then it's just a matter of...
I'm gonna take the mid of A2.
I'm gonna start at the position of B2 + 1.
So, in other words start right after the colon and then to figure out how far I want to go?
How many characters to use?
I'm gonna take C2 that's the place where the lowercase letter p is found minus B2 minus 1 because otherwise it would give me the p, as well I don't want to do that and enter that formula and sure enough It works no matter what time it comes in, whether we have 4 characters in the time or 5 characters in the time and also no matter what the price is, whether it's a security that is $1, $10 or $100 we'll still get the right value.
So, unfortunately It's not as easy as we thought but by using a couple of find functions in combination with the mid function.
We're able to solve this problem.
Thanks to Jason for sending in this question.
If you have a question please feel free to get the question to us either on a voicemail or in an email and we'll get to you on a future podcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Well, we're back in the office today.
I want to thank all of my friends and MVPs who provided podcast over the last ten days.
I hope you found that enjoyable.
Also, you know when I went to the sci-young thing on Monday, for opening day of Baseball.
Didn't realize that it had been raining there.
I threw the microphone down in the ground and of course it completely trashed the microphone.
So sorry for all the static, on Monday have a brand new mic.
Here today that hopefully will work.
Although, I have to wear this stupid mic but anyway.
We're back to questions today I have a lot of questions that came in over the last couple of weeks.
If you have a question please feel free to get it to us either leave us a voicemail or just drop me an email.
We'll get to it.
Today's question comes from Jason.
In the UK, Jason's getting stock quotes and the data comes in to column A and unfortunately, It has six bits of information right in that one cell and Jason just wants to grab the price out of there.
So, he wrote this formula he thought would work pretty well, the mid of A2 starting at position 13 for a length of seven and sure enough that does grab the 550.
But then Jason found that, there were problems if the quotes came in the morning.
So, let's Say 11:35 a.m.
That time took up one extra character and so the formula didn't work or if there were other securities where the price was only two digits instead of three digits then it's not working either.
Now, the solution to this unfortunately is rather complicated.
The first thing I did was, I took a look through that data and I said okay! Right before every price we have a colon. (:) So, if i could find the colon and then start to navigate from there.
That would be the first step.
Also, right after every price, we have a lowercase 'p' and so I used the FIND function and my first find function, when it initially worked I said I want to find a colon within cell A2 and I was surprised that It gave me a character position like 4 or 5.
Because what it was doing was finding the colon in the time and so I had to go to the third argument of the find function.
This says, hey! Start at position 7 that way I know that I'm beyond the time but basically it allows the find function to find the colon.
That happens after position 7 and it says that it happens in character 12 or 13 or 12 in row 4.
Basically, allows us to kind of hone in, on where the price is going to start.
It's gonna start a character after that position.
I thought I was gonna be in trouble, when I was finding the p because It might find the capital P.
and P M but It turns out that the find function is case-sensitive.
So, finding in quotes the lowercase p with an A2, will tell me that that p occurs in character position 20.
Then it's just a matter of...
I'm gonna take the mid of A2.
I'm gonna start at the position of B2 + 1.
So, in other words start right after the colon and then to figure out how far I want to go?
How many characters to use?
I'm gonna take C2 that's the place where the lowercase letter p is found minus B2 minus 1 because otherwise it would give me the p, as well I don't want to do that and enter that formula and sure enough It works no matter what time it comes in, whether we have 4 characters in the time or 5 characters in the time and also no matter what the price is, whether it's a security that is $1, $10 or $100 we'll still get the right value.
So, unfortunately It's not as easy as we thought but by using a couple of find functions in combination with the mid function.
We're able to solve this problem.
Thanks to Jason for sending in this question.
If you have a question please feel free to get the question to us either on a voicemail or in an email and we'll get to you on a future podcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
