Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Sort By Number Of Characters In Cell/column? How Please?

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

I want to sort a column of cells, by the number of characters (single words or sentences) in a cell. How can I achieve this please?

I'm very much a novice with excel so this is way over my head, can anyone help me please?

Thanks in advance for any help and guidance

View Answers     

Similar Excel Tutorials

Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Introduction to Using Filters to Refine Data in Excel
Filtering allows you to hide rows of data which you are not interested in so that you can easily look at the rows y ...

Helpful Excel Macros

Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
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.
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This

Similar Topics

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data


The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

Hi All
I hope that my following query will be clear. But if not, please excuse me I am new to Excel (well relatively!) and I'll try to be as straight forward as I can.

I have a column which contains 2500 cells. In each of these cells lies a description of an item. Each cell contains a max of 50 Characters (including spaces etc). 90% of these cells have less than 50, and 10% have exactly 50. I've just been told that instead of 1 cell of 50 characters, I should have 2 cells, each containing a max of 25 characters.

Now my problem is, I've already spent a huge amount of time editing the cells already and I don't want to have to go down through each cell and re-editing them in order to create two cells!

Also I tried the "Text To Column" Function. And while in theory this works, it means that it separates the cell at exactly 25 characters and anything beyond these 25 characters in put into the second cell. My problem with this is that it also chops my words up.

So i want to be able to create two cells, while also preseving entire words. So if that means that one cell must have 20 characters, adn the other 25, then so be it. As each will still contain less than or equal to 25...and most importantly the words are intact.

I hope I'm making sense.

I just think that there must be something that can be done to make Excel do this for me, rather than me having to manually edit 2500 cells of data.

Also if I can Excel to do it for 90% of the cells, i don't mind having to go in and edit 10% myself. I would except there'll be a few cells that it can't be done in, if there are the full 50 characters used, it would be lucky to have them separated at exactly 25 characters, and not breaking up any words.

Any help / suggestions/ prayers are appreciated!!

Just out of curiousity and since this seems to be the most active forum so..

What's the fastest way of reversing only words and sentences? :P
(without using excel's pre-established functions if any..)

Hello my name is kpark91

Reversing words: olleH ym eman si 19krapk
Reversing sentences: 19krapk si eman ym olleH

For reversing sentences:
I was thinking along the lines of treating the string as an array of characters..
1) Find length of sentence
2) Loop for integer value of length of sentence divided by 2 (swapping first and last characters, 2nd and 2nd last characters, so on...)

For reversing words:
1) Use Split function to split the words
2) Use the same method as reversing sentences except it's for each word.

I was thinking of using these methods but then I found out excel didn't treat the string as an array of characters

Any ideas? :D

I am trying to sort a worksheet out that will correct data when sent to us from an external company.

Basically what I am wanting to do is count the characters in a cell and based on the number use a formula:

if a cell has 14 characters


if a cell has 18 characters


if a cell has 19 characters


Any thing else then put the contents of the cell. all contents is coming from column A

There is about 8,000 records per day that has to be check.

Thank you


So, this is sort of confusing and was hoping someone can help with this formula.

I have a column of 4 digit numbers, and based on that column, I want it to grab a URL from a key of 4 digit numbers and URLs I have in another column.

After that... I want to replace the last 4 characters in the web address with 6 characters found in a seperate column.

You can see what I mean in my attachment. In column K... I'd want it to fill in a URL based on column B's four digit number (in a seperate tab I have sort of a key with 4 digit numbers and then a URL in the cell next to them). Once that URL is filled in (based on that 4 digit number in column B) I want to replace the last 4 characters (the URL's last 4 characters has the placeholder of XXXX) with the value in column A. I understand the REPLACE function to do this... but its moreso grabbing the URL from the list on the first tab that I am confused on.

I am referencing cells of a workbook (cells contain sentences/paragraphs)
from another workbook. When the workbook (doing the referencing) opens and
the user chooses "Update" several of the sentences are cut off in a fraction
of the cells. Is there a limit to the number of characters each cell
reference can transpose (for lack of a better word) from the second document?
If there is a limit, does the length of the file path to the referenced
document count toward the number of characters? Lastly, is there a way to
capture and display all of the information in each of the referenced cells
without truncating the information (maybe some sort of copy rather than
Please Reply,
Mike Nowak

I need to count the number of characters in a column of cells in excel.

Basically I need to make sure to clear any where the total number of characters 25 or less. This is due to the fact that I need to import this file into a database with a column field restriction.

So if J2 is: "12345678901234567890123456" then I need to clear it or at least identify it.

Conversely if J3 is:"1234567890123456789012345" then I need to leave it.

Any idea on how to count the number of characters per cell, and maybe possibly create a macro to clear the contents of any cell in column J which is over 25 characters?

Thank you

I have two columns of numbers (A and B). Each column is in text format. I inserted another column (C) and, in it, concatenated col A and B. The concatenation worked perfectly. The string is 17 characters long. Here is the problem:

When I try to sort on col C, it doesn't work. If I convert the concatenated text string to 'number' format, it cuts off the last two characters from the original something is going on in the cells that I can't see (some hidden characters, maybe?). This is driving me crazy, as I need a reliable way to join cols A and B, to make one unique identifying label (for lack of a better word) that I can then sort on.

Any ideas??? Thanks.

I am in the process of writing a macro that does multiple features. One aspect that I am having trouble is adding characters to a cell value.

Cell C25 would contain the number 26.

I would past that value to cell A2 on another worksheet, only I would need to value to be 000026.

I can't seem to figure out how to make it so that there are EXACTLY SIX characters pasted. If the number from the C column was not necessarily six characters, then zeros would have to be placed in front of it to make it six characters. The value for the cell in the C column can be anywhere from 0 to 999999.

Any ideas for doing this in VBA?


I need to sort a selected column by the number of characters it contains.

Any ideas ?

I need help separating data (60 characters max) that is in one cell into three seperate cells. There can be no more than 20 characters in each cell AND I cannot split whole words.

I have used the MID command to split data (<60 characters) in cell A1 into cell B1 [=MID(A1,1,20)], cell C1 [=MID(A1,21,20)] and cell D1 [=MID(A1,41,20)]. Unfortunately this process splits words exactly at the designated character locations, resulting in single words being split into 2 cells.

Any help?


In excel column i have same number of characters e.g. 6 characters but in alpha numeric mode in each excel cell of the column.The appearance of the characters are not of same width.

Can i make the changes using some vba codes, so that it looks same of the same width as the number of characters are same in each cell.


Hi All

I have used the search & find functions on numeous occasions to get the right or left most characters after a space.

However I am now presented with a new challenge but despite my best efforts have been unable to solve the problem at this point.

Imagine I have many characters in cell A1 example
"ABCD EFG HIJklhmsd 0xrt022ABCDEFGHXYZTYD1234567"

Some where in these fields I will have "022" where I find these characters I then want to see the next 12 characters after therefore in this example I would expect to see "ABCDEFGHXYZT"

I have used the search function succesfully in column B to find the characters
"=IF(ISERROR(SEARCH("*022*",A2,1)),"","A ")" this enables me to identify the cells where these characters appear but I have not been able to use the same formula with any success when used in conjunction with the right function to get the next 12 characters.

I would as always appreciate any advise and guidance on this one please

Regards Kevin


i want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:



I want it to look like:


Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.

How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.

thanks for each assistance


I have Excel 2003. I wonder how I do to fill all the cells in a column with spaces to X number of characters.


The column might look like:

123 (total 3 marks)
abcd (total 4 marks)
12abc (total 5 characters)

There are different variety of characters in each cell. I wish that if the number of characters is less than 5, it should be filled with blanks. Like this:

123"space""space" (total 5 characters)
abcd"space" (total 5 characters)
12abc (total 5 characters, no spaces needed)

I have trouble finding a formula that has an impact on the entire column, not just a single cell.
Ideally I would like to create a "blank template" in which these formulas are in the columns, so that when I then enter data into my cells, it should automatically be filled with spaces to X number of characters.

I found a formula that people said worked:
= A1 & REPT (" ", X-len (a1))
It doesn't work (replace X with the number of characters to be in total), the error message says something about circular reference. And it also only applies A1, I want it to apply to the whole column A1 to the last cell in the column.

Can anyone help me with this? I would be extremely grateful!

Less priority, but still a problem I would have to be solved is to:
Create "invisible" columns, where all cells are filled with Xnumber of spaces. Can this be done?

The reason for all this is that I would then save a. Txt or. Csv file. The program that import these files then looking for data on the position 1-4, 6-12, etc. So therefore I must have spaces in my data if a cell does not reach 4 characters, seven characters, etc.

EDIT: understand why I get the circular reference. If I use the formula in B1 instead, it works. But I do not want the result in a new column, for reasons explained above in the italic part.

I need to build a way to check sequential characters.

I have 26 characters (alphabet) and a long list of 7 letter words.
I need a way to check each word for how often a group of letters appears in words, The result will be counts of three to six letters. Letters are not necessarily contiguous.Double letters are not counted.

The program would search all words for letters starting with a minimum of three letters in sequence.

The result would be how many times a sequence of letters occurs.

I think an easy way to do this would be to first sort the word into alphabetical order

for instance
bangles would become abeglns
aground would become adgnoru

searches would become:
abeglns: abe=1,abg=1,abl=1,abn=1,abs=1,aeg=1,ael=1,aen=1,aes=1,agl=1,agn=1,ags=1,aln=1,als=1,ans=1
adgnoru: adg=1,adn=1,ado=1,adr=1,adu=1, agn=2 ,ago=1,agr=1,agu=1,ano=1,anr=1,anu=1,aor=1,aou=1,aru=1

I have incremented agn as it appears in both words.

I thought maybe the best way to do this is in Excel as I do not know any programming and I also want to be able to sort to show sequences that appear a certain number of time.

Is Excel capable of doing this?
Can someone advise me how to do it?


Quick question. Would anyone know if there is a way for excel to count the number of characters in a cell rather than have to count them manually. I'm working with CSV exports and am limited to the number of characters I can have in cells, therefore, I find myself manually counting the number of characters. Thank you in advance....

In a column of cells containing a mix of text and number characters, is there a conditional highlight formula that can highlight the cell within the column that contains the largest number of characters?? (i.e. the 3rd cell down has the most characters within)


Can this be done without a helper column also??

Hi there

I was wondering if somebody would be able to help me with some coding. Concerning 2 columns containing characters:



The rule I would like to implement is the following

- If the cell with the highest number of characters in column A is next to a cell in column B containing 0 characters, then highlight those two specific cells within the two columns.

Replies would be most appreciated. Thanks in advance.

Hi there

I was wondering if somebody would be able to help me with some coding?? Concerning 2 columns containing characters:



The rule I would like to implement is the following

- If the cell with the highest number of characters in column A is next to a cell in column B containing 0 characters, then highlight those two specific cells within the two columns.

Replies would be most appreciated. Thanks in advance.

I'm trying to keep the number of complete sentences to the maximum number
within a 400 character limit. I understand how to use LEN to limit the
number of characters, but don't get how to take the resulting cells that have
incomplete sentences and delete the partial sentence at the end.

Any advice would be appreciated.

Hello, I need some help with splitting a single data column into 2 values.

I have a single column that looks like (3 examples):

N210-450E @ south side of bridge
N211-450E 100ft from railroad track
N212-450E next to the stop sign

I would like to split the entire value into two columns. The first column would be the first 9 characters (always) which is the 'N210-450E' value and the rest of the characters go into the 2nd column (variable number of characters).

How do I do this?

HELP! I am trying to find a way to limit the number of characters in a cell or range of cells in a spreadsheet. I need for it it to STOP the user from further typing after 250 characters or words. Can this be done?

I have a workbook that I have used for years and today for the first time I
found the columns and rows would not sort properly in one of the worksheets.
The reason was obvious. Three cells in the same row, one numeric and the
others text, had a heavy vertical bar followed by a lozenge at the end of
each entry as shown on the Formula Bar. The row of data in question is the
most recent one added to the middle of the sheet. I have no idea what
those characters mean nor how they got there. I deleted them from the end
of each of the cells and everything is now fine.

Even more interesting to me is that the same sheet on another machine did
not show the characters in the Formula Bar but it would not sort correctly

Would someone give me some help in understanding what these characters mean
and how they might have gotten there?

Thank you very much.

hey guys

i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.

thanks in advance