 |
 |
| |
|
Back to Excel Newsletter Archives
 Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help Excel Answers To Your Google Toolbar (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