Excel has many different functions that can help you parse out text from cells. This includes the LEN, MID, LEFT, and RIGHT functions. By utilizing these and other functions, you can get just the values you want. And by determining the number of blank spaces within a cell, you can also determine the number of words that a cell contains. There are multiple ways you can count cells in Excel, I’ll start with using the easier, and newer TEXTSPLIT function.
Method 1: Counting words using the TEXTSPLIT function
The TEXTSPLIT function is available for users who have Microsoft 365 and so if you do not see that function available as you type it in, you’ll need to move to the second approach. Using the TEXTSPLIT function, you can turn a single text value in a cell into multiple cells or columns. And you can specify how you want to split a cell; which delimiter you want to use.
In the example of counting words, the delimiter you would use is a blank space, as specified with ” ” in the delimiter argument. Here’s a list of article titles that I am going to use for this example:
The article titles are in column A. The formula to split the text every time there is a blank space would be as follows, assuming the first value is in cell A2:
=TEXTSPLIT(A2,” “)
This formula, however, would simply put all the words in different columns. Thus, it is incomplete when your goal is to count the number of words. To fix this, the formula needs to be embedded within the COUNTA function. How COUNTA works is that it simply counts the number of nonblank values.
=COUNTA(TEXTSPLIT(A2,” “))
Copying this formula down, these are the resulting values and the number of words found in each cell:
Here’s a closer look at how the formula in B2 works, using the Evaluate Formula feature in Excel:
The TEXTSPLIT function is breaking out each word as its own separate value. And the COUNTA function is then counting each one of those values. When combined, these functions allow you to count the number of words in a cell.
If you’re using Google Sheets, you can use the exact same formula as shown above, with the only difference being that instead of TEXTSPLIT, you’ll use the SPLIT function. It works in the exact same way.
Method 2: Using the LEN and SUBSTITUTE functions to count words
If you are on an older version of Excel where TEXTSPLIT isn’t available, there’s still a way that you can count the number of words within a cell. It will be a slightly more complex formula that will use the LEN and SUBSTITUTE functions.
The first part of the formula will involve counting the number of characters in a cell, which is what the LEN function does. This is accomplished through the LEN(A2) formula — assuming that A2 is where the article name is.
Next, you’ll need to use the SUBSTITUTE function to replace the blank values ” ” with an empty string that just contains two quotes: “”. To do that, the formula for that portion would be: SUBSTITUTE(A2,” “,””). This formula will need to be enclosed within a LEN function. What this accomplishes is it counts the number of characters in the cell after you’ve replaced all the blank values. If you take the total cell length and subtract this second piece, you’ll be left with the number of blank values in the text.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)
However, this isn’t entirely correct as you will be off by 1 word. This is because since the formula is counting the number of blanks, it won’t include the first word, which doesn’t come with a space before it. That also means if you only have one word, you’ll have a value of 0 instead of 1. To fix this, you’ll simply need to add a +1 to the end of your formula.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)+1
This would mean, however, that even blank cells would return a value of 1. And this would technically be the same problem when using the TEXTSPLIT function as well, since it doesn’t check for blanks, either. To correct this, you can simply add an IF function to check if the value is indeed blank. Here’s how the full formula looks:
This will return nothing if the cell is blank. If the cell isn’t blank, then it will go ahead and perform the rest of the calculation. As mentioned, this IF function can also be added to the start of the TEXTSPLIT function as well.
If you liked this post on How to Count Words in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
If you want to download a company’s financial statements or data, the easiest place is often straight from the source: the Securities & Exchange Commission (SEC). You can download financials in Excel format if there is an interactive option within the SEC filing, but that won’t give you all the tables contained in an earnings report. In this example, I’m going to use Adobe’s most recent earnings report to show you how to get a table into Excel
Downloading the data
Adobe’s earnings report is found here, with the following financials on page 4:
Copying it into Excel
Copy the table and then go to paste it data into Excel. But when you right-click in Excel, make sure to select the option to paste it so it matches the formatting on the sheet, as shown below:
Now, the data pastes without any of the colors and formatting onto my Input sheet:
If when you paste it doesn’t show up like this and it looks like just a few lines, re-try copying the data. It may help not to include the header that says “three months ended” and simply start copying from the first line item (“revenue” in the above example”) to ensure that Excel picks it up as a table.
Formatting the data
It looks pretty good except that I have many extra columns. And numbers that have dollar signs have been pushed out by one column. What I will do here is create a template in a separate sheet that will automatically pull in what is needed. The new tab, called Output, will be where I create my formulas. My assumption is that the spacing will be consistent and that the current period values are in columns D and E, and the ones from the prior-year period are in columns J and K.
Starting in cell A1, I’ll create a simple formula that checks if the same value on the other sheet is blank. If it isn’t, then it will pull in the value, otherwise, it will remain blank:
=IF(Input!A1="","",Input!A1)
I will do the same thing for column B, except this time I am looking at values from the Input tab in column D. And I will need to adjust for if there is a $ sign. If there is, I need to pull the value from column E instead. Here’s what that formula looks like:
That gets me a bit closer to where I want to get to:
There are still a couple of issues. The first is that on row 30, there is a symbol that isn’t a dash that I need to remove. This is character code #151. And there’s also a trailing blank space behind the numbers that needs to be removed. This isn’t your ordinary blank space and it is character code #160. I need a couple of SUBSTITUTE functions to remove those character codes:
For character 151, I want to replace this with a 0 value since that’s what the symbol is in place of. Next, I need to convert these values to numbers. I can do this by multiplying them by a factor of 1. I’m going to use the IFERROR function as well so that in case it’s text, it will return the original value in column D. Here’s my completed formula:
Now, I can repeat this formula in the adjacent column. Except this time instead of referencing D and E, I’ll refer to columns J and K. Now, my output tab looks as follows, after applying some formatting to it:
This can be re-used over for other tables in an SEC report, as they generally follow the same pattern. For example, this is Adobe’s table showing sales by segment:
By dropping this into my Input tab, this is what my Outputnow shows:
All that I needed to do was to copy the formulas and just adjust the columns they referenced on the Inputtab. If you’d like to use the file I’ve created for your own use, you can download it for free, from here.
If you liked this post on How to Convert a Table From an SEC Report Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.
Step 1: Compiling the data
You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.
To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:
=GOOGLEFINANCE(“.DJI”,”price”)
And to calculate the percentage change:
=GOOGLEFINANCE(“.DJI”,”changepct”)/100
For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”
For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:
Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:
In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:
Now, it’s time to pull all this data into Excel.
Step 2: Loading the data into Excel using Power Query
To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:
Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:
On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:
To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:
Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.
Repeat these steps for the other Google Sheets tab.
In addition, I’m also going to load data from a few other sources:
Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100
Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100
Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/
The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.
Step 3: Creating the dashboard
In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.
To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.
First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:
To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):
=E$6<0
This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:
For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:
For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.
To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:
Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:
To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:
These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.
Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:
Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.
Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.
In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):
That will create a new column:
In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.
After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:
Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.
The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:
To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:
Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:
SUBSTITUTE([@Volume],”B”,””)
In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:
SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)
I multiply this by the first part of the formula, and my numerator is as follows:
For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:
The -1 at the end is to put the change in a percentage of less than 100%.
Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:
#,##0%;[Red]#,##0%
The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.
Here’s how this section looks as part of my dashboard:
And here’s a snapshot of the dashboard as a whole.
One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.
Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:
Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.
If you liked this post on Creating a Stock Market Dashboard in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Depending on what kind of data you are working with and how you need to present it, you may need to show numbers in thousands, millions, or billions. Below, I’ll show you how you can quickly and easily make those conversions. You’ll also learn how to add a letter behind each number to indicate either B for billions, M for millions, or k for thousands.
Converting between billions, millions, and thousands
If you are dealing with raw numbers, to convert millions into billions you only need to multiply them by 1,000. And you don’t need to convert one value at a time. To multiply an entire range, copy a cell that contains the number 1,000 (or whatever factor you want), select the range you want to multiply, right-click paste special and you will see the following options:
Selecting the Multiply option will multiply the cell against each one of the values in the range. If you wanted to reverse the calculation and convert billions into millions, then you would follow the same steps except instead of selecting Multiply, you would choose to Divide. You can use this for other operations as well, including addition and subtraction.
Another potential use you may have for this is if you have numbers that Excel is recognizing as text. Multiplying all of them by a factor of 1 could fix that. And multiplying by -1 would flip their signs if you needed to switch them from positive to negative, or vice versa.
However, in some cases, things can be a little more complicated and you need to do more than just multiplication. When you are looking at stocks and trading volumes, for example, you may see abbreviations such as B or M. Here’s a look at some of the best-performing stocks from March 10 and their trading volumes, as per Yahoo! Finance:
While most of them contain M for million, some of the numbers are in thousands. Simply getting rid of the M wouldn’t fix this problem as then the numbers in millions would appear smaller than those that are in thousands. To fix these values, we’ll need to do two things:
Get rid of any letters.
Scale the numbers consistently.
To avoid the numbers getting too long, I’ll convert these numbers all into millions. That means for numbers that have an M, I only need to get rid of the letter. And for thousands, I need to convert those numbers into a fraction of 1 million.
This is going to require an IF statement to correctly convert all of the values. The first thing that needs to happen is to determine if the number is in thousands or millions. This just requires using the RIGHT function, which will tell us the last letter or number in a cell:
=IF(RIGHT(A1)=”M”
Where A1 is the cell that contains the value. If this test evaluates to true, then the next step will be to get rid of the letter using the SUBSTITUTE function. Since I’m leaving the values in millions, I won’t need to multiply or divide the value by anything besides 1. The formula will now look as follows:
=IF(RIGHT(A1)=”M”,SUBSTITUTE(A1,”M”,””)*1
I replaced the “M” with a blank value. I also need to multiply everything by a factor of 1 to make sure it reads as a number. Otherwise, it would simply be text.
If I also had billions in my data set, I might use another IF statement here and do the same thing, only instead of multiplying by 1, I would multiply by 1,000 to arrive at millions. For example, $1B would become $1,000.
However, the data set doesn’t include billions and so I only need to account for thousands. The remaining values that aren’t millions I can just divide by 1,000,000 to determine what fraction of 1 million they are. The factor has to be this large because the numbers are raw and aren’t in thousands.
Now I can copy this formula down across my data set, and this is how it looks:
The numbers that were in millions simply lost the ‘M’ at the end of their values. And those that were in thousands now are in decimals, indicating how much of 1 million they are. For 342,271, it now shows 0.342271.
This is a complex example where you are dealing with text and the important thing to remember is that once there are letters involved in a number, the value automatically becomes text. If you want to apply some sort of calculation, it is going to be necessary to convert it back to a number — after you have gotten rid of any letters.
How to show numbers with B, M, or k
Next up, let’s take a look at how you can add letters to an existing number. Essentially, I am going to undo what I did above. Let’s start with turning our decimals into thousands. To do this, I can look for if a value if less than 1. If it is, then I will multiply it by 1,000 and add the letter ‘k’ to the end of it. Here’s how that formula will look:
=IF(A1<1,A1*1000&”k”
My value of 0.342271 becomes 342.271k. However, if I don’t want the decimal places and I want to round, I can adjust my formula accordingly:
=IF(A1<1,ROUND(A1*1000,0)&”k”
Using the ROUND function and setting it to 0 decimal places, I round up and now my value shows as 342k.
Next, I’ll need to add an “M” if the number is in millions. If any of the numbers were in billions, what I could do is check if a number is 1,000 or greater (e.g. 1,000 million). But since I don’t have billions in this data set, I can just simply add an “M” on to everything that is not in the thousands:
=IF(A1<1,ROUND(A1*1000,0)&”k”,A1&”M”)
This is what my values look like after this latest conversion:
For argument’s sake, I’ll change the first value so that it is 1,536 and show you how I would adjust for this calculation if that were $1 billion. As mentioned above, I would check if the value was more than 1,000. And if it is, I will divide it by 1,000 and add a “B” to the end of it. My formula, accounting for millions, billions, and thousands, will look like this:
The reason I leave the millions calculation last in that formula is that I know if it isn’t less than 1 (thousands) and if it isn’t more than 1,000 (billions), then it has to be millions.
Remember: by adding letters to these numbers, they can’t be used in any sort of calculations. And so before you decide to go that route, it’s important to consider those limitations.
If you liked this post on how to convert numbers from billions to millions to thousands in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
When copying data into Excel from other sources – be it web pages, documents, or other Excel documents sometimes you end up with unwanted formatting, such as numbers with invisible spaces that prevents it as being read as a number or converted into one. Or just ending up with data you don’t want.
The TRIM function in Excel is useful for removing trailing spaces after text that serve no purpose.
The more annoying issue that I’ve come across is a blank space that looks like one but isn’t. It is usually character # 160 which looks just like a blank space, only thinner than normal. If you enter char(160) in a cell you’ll see what this looks like. It can be a frustrating process because this character you can’t get rid of by just searching for blank characters and doing a find and replace or the trim function. It’s possible to see this with other characters as 160 isn’t the only one that looks like a blank space, but it is the only one I’ve come across so far.
The solution is to replace character 160 with character 32 (this is a normal space that will get eliminated with the TRIM function). To do this, use the following formula, assuming cell a1 is the cell that needs the cleanup:
=TRIM(SUBSTITUTE (A1,CHAR(160),CHAR(32)))
You can add a *1 to the end of the formula to convert it to a number if necessary.
See below for an example. The only difference between the cells in A2 and A3 is A2 has a normal blank space after it whereas cell A3 has character 160 after it. Both cells equal the same length (12 characters) as show in column B. column C is what the cells look after using the TRIM function – they will look the same but column D recalculates the length and the top cell has now gone down by one character (the trailing space). Column E is after the above formula is used. You can see now the updated length in column F is 11 for both, meaning the trailing character has been deleted in both cells.
As a side note, if you’re need to reference a specific character in excel you’ll notice all it takes is using the CHAR formula. If you’re not sure which number of the character you need, you can have the CHAR function reference the numbers 1 to 255 and you’ll see all the different characters available.
