Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

   Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter August 2008

Add to Google advanced search!
Create Excel dashboards quickly with Plug-N-Play reports.

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help

Excel Answers To Your Google Toolbar (Details)

Got any Questions? Free Excel Help


FREE VIDEO TUTORIALS

Thanks to YouTube (Google) and others who make the videos, we have added Excel video tutorials to our site on various pages. To see the whole lot (over 70 and growing)see this
page
.  You should also notice that on the left there are links to Video Tutorials From Microsoft.

EXCEL TIPS AND TRICKS

Count Lines in a Range of Excel Worksheet Cells

To count the number of lines in any cell with Wrap Text on, use

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1

Keep in kind that the formula above WILL return 1 for an empty cell, due to +1. Overcome that problem by use of this function;

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+(LEN(A1)>1)

The +(LEN(A1)>1) will either return TRUE (1) or FALSE (0).

f more than a single cell, you can repeat the formula for each cell in the range and SUM the results (My preference). Or use a single cell and nest the formula above with a
SUM. E.g

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+(LEN(A1)>1),LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+(LEN(A2)>1)...etc)

Count Words in a Range of Excel Worksheet Cells

Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. However, with the help of the SUBSTITUTE function/formula and the LEN function/formula we can. If you are not familiar with these functions/formulas I have written an explanation below.

SUBSTITUTE

Syntax
=substitute(text,old_text,new_text,instance_num)

What it does
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Example
=SUBSTITUTE(A1, "Sales", "Cost") If A1 had the text "Sales Data" the formula result would be "Cost Data".

LEN

Syntax
=len(text)

What it does
LEN returns the number of characters in a text string.

Example
=LEN(A1) If A1 had the text "Sales Data" the formula result would be 10 as A1 has 9 text characters and 1 space character.

Count Words in a Cell

The formula below will return the number of words (not characters) in cell A1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Be aware that superfluous spaces are also counted and may give misleading results. To ensure accuracy we can simply nest the TRIM formula  function/formula in the first LEN

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Count Words in a Range of Cells

The formula below will return the number of words (not characters) in cells A1:A5

=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+5

Or

=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+Rows(A1:A5)

Keep in kind that the formula above WILL return 1 for an empty cell, due to +1. Overcome that problem by use of this function;

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A3)," ",""))+(LEN(A1)>1)

The +(LEN(A1)>1) will either return TRUE (1) or FALSE (0).

See Also: Summing, Counting & Pivot Tables in Excel

 

EXCEL VBA TIPS & TRICKS

Transpose Single Column List Into X Columns

Sub TransposeRows()Dim lRows As Long, lCol As LongDim rCol As RangeDim lLoop As LongDim wsStart As Worksheet, wsTrans As Worksheet    On Error Resume Next    'Get single column range    Set rCol = Application.InputBox(Prompt:="Select single column", _                                    Title:="TRANSPOSE ROWS", Type:=8)                                        'Cancelled or non valid range    If rCol Is Nothing Then Exit Sub        lRows = Application.InputBox(Prompt:="Transpose every x rows", _                                    Title:="TRANSPOSE ROWS", Type:=2)                                        'Cancelled    If lRows = 0 Then Exit Sub                                        'Make sure the transpositions will fit    If lRows > ActiveSheet.Columns.Count Then        MsgBox "Your 'transpose every x rows' exceeds the columns available"        Exit Sub    End If        'Limit range to used cells    lCol = rCol.Column    Set rCol = Range(rCol(1, 1), Cells(Rows.Count, lCol).End(xlUp))        'Set Worksheet variables    Set wsStart = ActiveSheet    Set wsTrans = Sheets.Add()    wsStart.Select        'Loop with step of x and transpose    For lLoop = rCol(1, 1).Row To Cells(Rows.Count, lCol).End(xlUp).Row Step lRows            Cells(lLoop, lCol).Resize(lRows, 1).Copy            wsTrans.Cells(Rows.Count, "A").End(xlUp)(2, 1).PasteSpecial Transpose:=True            Application.CutCopyMode = False    Next lLoop        On Error GoTo 0End Sub

Got any Questions? Free Excel Help

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help