Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Average Of Text

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is it possible in XL2003 to get an average based on text values.

For example in each row in a single column I have text values like: Single, Married, Divorced, etc...

Can I get a single result return in text format for the average text data recorded in that column.

A2 = Single
A3 = Married
A4 = Single
A5 = Divorced
A6 = Single

i.e. the total majority of data = Single

View Answers     

Similar Excel Tutorials

Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
Understanding Formulas and Functions in Excel
In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A formula is an expression whi ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell which you have given a cus ...
Create and Manage Tables in Excel
Here, I'll show you everything you need to know to get started using tables in Excel; how to create, edit, and man ...
Complex Structured References (Table Formulas) in Excel
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...

Helpful Excel Macros

Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Show a Single Comment in Excel - Means the Comment is Always Visible, No Hovering Necessary
- This macro in Excel allows you to display or show a particular comment in Excel. This will make the desired comment vis
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f

Similar Topics

Hello. I wrote the code below to sandwich data in a cell between single quotes. I was forced to used two single quotes in the first concanate portion because as you know excel treats one single quote at the beginning of a text field to be non-formula. Do you see any issues with my code adding two single quotes at the beginning? It seems to work, but I don't want to be surprised one day when it might not. Thank you.
In case it is hard to read, the string part of my code is double quote, single quote, single quote, double quote, &cell.Value&, double quote, single quote, double quote.

Sub AddSingleQuote()
Dim n As String
Dim cell As Range
For Each cell In Selection
cell.Value = "''" & cell.Value & "'"
End Sub

Hi all

I'm trying to find a way to split a single column of data across multiple sheets.

The situation is I have 60 sheets in a workbook all in identical format, with different data.
On a single workbook I would use "text to columns" function and then delimited based on splitting using spacing.
How do I do this across 60 sheets as I would with a single sheet?

I have a spreadsheet where one column has an excess of text for each row entry, but I don't want to use text wrap. I'd like to keep the row height consistent throughout the document, limited to one line of text.

Is there a quick/easy way to reveal the excess text that doesn't fit in a cell? Can it be done in a paragraph format with returns, as opposed to a single long horizontal line of text? All this, and when not selected, return to a single height row?

How can I import a whole text field into a single cell in Excel, without the line breaks making Excel start a new row? I'd like the whole text document in a single cell.

I know I could copy and paste but I want to automate it.

Eventually, I would like to write a script to go through a whole directory and import each text file into a different cell of my spreadsheet. But small steps...

I have a couple columns in a spreadsheet I am working on that require a text ' (single quote/apostrophe) to be the first character in the cell.

I can enter the single quote and can see it when editing the cell but the single quote is not displayed on the screen?

Any ideas? I did some searches on this site but could find nothing regarding this. I assume Excel is treating it as some sort of control character. I changed the column type to help. If I put a space in front of the quote it does work but I would rather not have this space in the cell.


I am wanting to take several paragraphs from another document and paste this into a single cell within Excel but no matter what I try, when pasting it wants to spread the paragraphs across multiple cells. I am also wanting to have the Cell that the data is pasted to wrap the text (ie keep the column width).

Any suggestions on how I can get the paste to work within the single cell.

Much appreciated



I am going to save an Excel worksheet into tab delimited text file for importing into another application.

I notice that contents in some columns contain single quotation mark at the front of each string of text. I understand this is to 'force' the contents to be in text format.

How can I remove this single quotation mark? I do not want them to be in the tab delimited text file.

I am using Excel 2010

I have various text documents that I've pasted within Excel.

On some occasions the entire document fits inside a single cell, in other cases the document overlaps several rows (dividing each sentence to its own row/cell )
I understand its a formatting issue, however I'm trying to duplicate this within excel.

If I type a large body of text( a few paragraphs of common sentences) within a single cell and paste this into another cell. How can I format this such that it fills into the lower rows (ie: a new row per sentence)

Thanks for any help


Is there a way to change the text in a multiline userform textbox to a single string when adding the text from the userform into a sheet cell.

I want to remove all line breaks so the text is a single line?

thank you

Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.

Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.

Any help anyone could provide would be WONDERFUL. Thanks!

Morning all,

Using Excel 2007.

Is it possible to combine a text value and a vlookup into a single cell ?

By way of example what I have at the moment is the text "Batch 1:" in cell A10 and a lookup in cell B10 which pulls through a comment on Batch1 from another source.

The problem is that cells A1 to A8 contain further text in each cell. Now this text is a lot longer than Batch 1 but it still needs to be seen.

So what I have is :

Batch 1: Then the result of my look up further over the sheet than I would like (it amounts to about 30 spaces away).

So, I was wondering if it is possible to combine the text Batch 1: and the vlookup into the same cell?

All help appreciated.



I have a large data set where a single account is represented as a fixed number of rows (i.e. 5) in a single column. In other words, the first 5 rows in the column represent a single account. The next 5 rows represent the next account, etc.

I want to convert each set of 5 rows into a single row with 5 colums so that I can sort and aggregate the data. How can I convert every 5 rows into 5 columns in a single row?

For example, I want to convert this:

into this:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

Is this possible to do this in a manner that could be easily be repeated to accomodate 10-15 thousand rows of input data in this format?

Thanks for your help.


I am trying to drag a bunch of text files into an Excel workbook. I would like each text file to be its own worksheet inside a single workbook. When I click and drag files over into Excel, it opens a new workbook for each file. Is there a way to drag them into a single workbook as separate sheets rather than individual workbooks?


Hello All,

This is my first post here and looking for help.
I am working with a database which does not let me import data. So i have taken screen shots of the data, cropped the relevant data needed using MS paint and pasted the images on a word document.Next converted the word Doc into PDF and then using a OCR, have converted the images to editable text. Next have copied the converted text into excel.

Now I need help formatting the data. Have succeeded in separating the single column into multiple columns using the de-limited format and removed the parenthesis using replace function

Need help on the following

1) Converting the multiple rows of data in each paragraph in MS word into a single column
2) Pasting the different paragraphs in the word document into different columns.

Any and all help will be much appreciated. Am attaching the files for clarification.

Thank you very much


Hello Board,
I have a list of 3,000 customers email address, currently in a single column, I need to paste them into a single cell (or as many as possible into a single cell) separated by a comma, so I can drop them into a bulk emailer on our website,

I have concatenated a comma on to the end of each one, but need a way of putting the comma'd addresses into the single cell, without selecting the cell typing a comma then the next cell etc..

I thought that I could paste special transpose, the list and then 'merge' it into one cell?

Any thoughts, ideas

Ps the email address list is genuine - (and not for sale!)

I use the Import Text Wizard to paste comma delimited text into an Excel
spread sheet. I end up with a single row of multiple columns. One column for
each piece of delimited text. What I really want is delimited text in a
single column of mutliple rows. One row for each piece of delimited text. How
can this be done?

I have 1,000 data in Col D (rows 5 through 1004);

I want to copy the first 12 to Col L (rows 5 through 16) where I will do several
calculations to end up with a single average in cell M1;

I now want to copy a second set of 12 from col D which are in D6 through D17 again
into rows 5 through 16 of Col L where again my calculations will end up with a single
average in cell M1;

And so on through the entire 1000 data in Col D, always plucking 12 into col L (rows
5 through 16) where I'll do calculations that end up with an average in M1.

I would like to run through the 1,000 (perhaps by hitting F9 for each set of new calculations) and save the 1,000 data placed in M1.



When I paste text that contains paragraph marks into Excel it breaks the text
at each CR into a different cell. How can I force Excel to copy a body of
text, including CRs, into a single cell.




I have been working on converting a large data set with data sampled every 5 seconds into a set of data which averages all of the data points over each minute and reports a column of data with one average data point corresponding to each minute.

Is there some way to have excel take the average of every set of 13 datapoints in a column and report each of these averaged values in a single column. The default from excel will take the average of the first 13 datapoints, i.e. AVERAGE(A1:A14) and then using autofill instead of reporting the average of the next 13 data points in the set i.e. I would like it to be AVERAGE(A15:A28) followed by AVERAGE(A29:A42) etc, but Excel defaults to AVERAGE(A2:A15) followed by AVERAGE(A3:A16) etc. instead.

I was hoping autofill might work for this in someway, but it doesn't seem to be doing so.

I am not totally ignorant of excel, but am far from an expert so any help is greatly appreciated and you will have to accept my apologies if this is a very simple problem.

I want the AVERAGE function to be able to include cells which also contain the "<" character.
At the same time, I want cells containing the "<" character to use 50% of the reference value for purposes of the average calculation. I have attached an example of the output I'm seeking.

I would like for the solution to be a formula or array I can put into the calcuation cell without the need for dummy cells or off-page conversions or calculations. I can do that, using a single formula is what I'm struggling with.

Sorry if that's a bit long winded...

Here's how it breaks down.

If I have, in one cell (call it D1):


and in a lookup table on another sheet:
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...



Does that make sense or should I resubmit?

I've tried a few different ways but can't come up with anything...

There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.

Cheers guys! Great forum!

I am trying to determine the average, stdev, min, max of a column of data that contains #N/A (empty) cells. I'd just like to ignore all the #N/As in the column.

How best to do this?

Here's a sample

I tried =AVERAGE(IF(ISNUMBER(P2:P65), P2:P65)), but I think that is not calculating anything if a single #N/A is found.


Hi all, just wondering is it possible to insert a hyperlink on a single word in a cell containing text, rather than on the whole cell? Or to have more than one hyperlink in the cell, each referring to a single word? My initial research says no, but I would be delighted if somebody could prove me wrong.



Hello everyone,
does anybody know what does exactly DoubleClick on a cell do
and how to apply this action on a selection of cells?

I have a column formated as GENERAL. At the same time I need to make part of that column TEXT. I use single quote (') in front of value to let Excel know that it is TEXT type. I use formula to put single quotes (') into required cells.

However, Excel doesn't accept the single quote (') as a control symbol untill I double-click on the cell.
I need to repeat this double-click for each cell.

Is there any way to apply this action (double-click ) on a selection of cells?

Thank you.