Everyday I am a bit more happy with this blog. The WordPress team did a great job as it is reliable and it’s bundled with useful features. You’re going to ask me: what’s the link between WordPress and Excel?
Well: Thanks to WordPress I know what you are looking when you use the search engine in the top right corner.
Even though this won’t be enough for me to take on Google and Yahoo this is more than enough to know that some of you have problems manipulating text fields in Excel. Apparently you are looking for a “Between” function in excel to get info from a cell.
For those who have such problems let me introduce you to your 3 new friends: left(), right() and mid() (it could be middle() in versions before Excel 2007)
Please, bear with me as it’s going to be veryyyyy long to explain 
A1 is the the cell you want to trim.
Here it says “i love chicken rice in singapore and this blog is fantastic” (the only thing that came up to my mind was my bank account password )
Let’s say you want to get:
- “I love chicken rice in Singapore”: Use left()
Here I am using (A1,27) meaning I want to get all the characters from 1 to 27 in A1.
- “this blog is fantastic”: Use right()
Here I am using (A1, 19) to print “thisblogisfantastic”. Basically I want to get the last 19 characters out of A1.
- “singapore”: use mid()
Here the inputs are (A1,19,9). That means that in the cell A1 I want to ignore the first 19 characters AND the last 9 characters in A1.
Wow, so difficult…
Some may have problems understanding how useful these functions can be.
Well, let’s say you have a series of Tickers from Yahoo Finance in an Excel spreadsheet that looks just like in column A:
Using the function mid() as shown in H1 you see you can retrieve the tickers easily.
Note:
Tickers do not always have the same length so there may be errors here and there.
For instance A1 and F1 or A7 and F7.
You can solve this easily by editing the value you use when calling the mid() function.
Was this helpful???
