Back to Excel Newsletter Archives

Complete Excel Excel Training Course. Instant Buy/Download, (Details) |

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

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)

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

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

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

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

**FREE Excel Help **