# OzGrid Excel and VBA Newsletter August 2008

#### 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.

## 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).

## 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