Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Sort on Multiple Columns at Once in Excel
How to sort a data set by multiple columns in Excel.  This allows you to better organize a data set and is really e ...
Case Sensitive Sorting in Excel
How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...
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 ...

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

6
[6
6]
[6]
7
12
14
14]

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




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 concatenation...so 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.


Hi

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?

Michael

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.

Thanks


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


Hello!

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

Example:

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?

Thanks


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)

ab
abcdh8k
ab4cdefghijkl
fglmn
lmno6
pq
pqr1

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:

COLUMN A:
x
xx
xxxxx
xxxxxxxxxxx
xxxx
xxx
xx

COLUMN B:
xxxxx
xx
xxx
xxx
xxxxxxx
xx
x


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?


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
regards


Is there a way of Limiting the number of characters in a cell?

I am working on updating a database and need to have a maximum of 22 characters in the item description field in order to print labels for retail.
ie: can one format a column to accept up to a specified number of characters?
Thanks, Art




Is it possible to create a VBA custom sort looking at only the first 3 characters in the cells. I'm trying to sort a huge list of item numbers into catagories. the first three characters can identify what catagory it belongs to.
for example there are two catagories Roadway and Pavement
these are the item numbers
202E23500 would go with Roadway
254E01000 would go with Pavement
407E10000 would go with Pavement
202E01000 would go with Roadway
209E72001 would go with Roadway

(this is drastically simplified)

so you see the first three characters (202 and 209 for Roadway 254 and 407 for Pavement) is what I want to sort off of
I can make a list to base the custom sort off of which three characters go with each catagory. There are just way to many item numbers to create a custom sort base off the whole number.

I would like that example list to Sort like this

Roadway
202E01000
202E23500
209E72001
Pavement
254E01000
407E10000

I can add the Catagory names in the column with the Item numbers to make it easier to sort.

any help with this code would be awesome I can edit the code to make it apply to the whole set of data but i just dont know if this is possible or how to do it.

JD

I need to create a load column that contains data from 4 different columns, with predefined lengths. Not all of these characters will be used, but can be. I need to concatenate these so that if only 5 characters are used in the first column, the remaining 15 will be filled with spaces.

Column A = name (20 characters)
Column B = depth (10 characters)
Column C = Thickness (10 characters)
Column D = Subsea (10 characters)
Column E = Combo of above (50 characters + 3 commas to separate)

Is there a way to do this?? Does this make sense? I have tried custom formatting, to no avail, but maybe I am missing something.

Thanks,
C




How to find the number of characters in a cell of specific column (for example column 8)

Lets say I have Cells filled up with some characters in column 8

Column 8
AA
BB

CC
IIIII
EEEEEE

Result should be ( assume Column 8 is the last column)
Column 9
A
A
A
I
E

I want to write a macro that finds the number of characters in a cell and if that number is less than 3 then assign "A" to the last active column +1
If number of characters are greater and equal to 5 then write 4 th character in to the last active column+1
If the number of characters =0(Empty) Then delete the whole row.

I am new to VBA so I don't know how some syntax work like the Len(), but the Idea is this:

Option Explicit
Sub Check()
Dim myRange As Range, sRange, Sheet1 As String
Dim I, ROWNUM, COLNUM As Long
Dim ws1 As Worksheet


ROWNUM = Worksheets(sheet1).Cells(Rows.Count, 1).End(xlUp).Row
COLNUM =Worksheets(sheet1).Cells(1, Columns.count).End(xlToLeft).Column
Set ws1=Worksheets(Sheet1)

For i=1 To ROWNUM
If Len(8)<3 Then
ws1.Cells( i,COLNUM +1)="A"
Else If Len(8) >=5 Then
ws1.Cells( i,COLNUM+1)=Mid(ws1.Cells( i,8),4,1)
Else If Len(8)=0 Then
Rows( i).Delete
End if

Next i

End Sub

Please Help!!!
Thanks

Greetings,

I have several excel cells where there are two of the same words together, for example:

JamesJames

or

laptoplaptop

Is there a formula or something can delete half the characters in a cell?

Thanks in advance.

- Mitalis


Hi All:

I seek the knowledge of the experts once again...

Can you tell me if it is possible to have the active cell change after a certain number of characters are entered?

I have a spreadhseet that people enter a 36 digit code into. The different fields have a different amount of characters in them. The string is as follows:

J17 can enter 3 characters
K17 can enter 6 characters
L17 can enter 4 characters
M17 can enter 6 characters
N17 can enter 6 characters
O17 can enter 4 characters
P17 can enter 4 characters
Q17 can enter 4 characters

SO what I would like to happen is the user enters the 3 digits in J17 and then the active cell becomes K17 where they enter 6 digits and then the active cell becomes L17... Does this make sense? Currently the user has to Tab to the next cell but if they could enter the number contu=inous that would be great.

Any ideas?

THANKS,
Mark


Hello everybody,

I'm looking for a way to sort characters alphabetically within a cell.
So if a cell would contain the text 'Excel', I would like to show 'cEelx" in another cell. Someone any idea?

Bodtje


I am using the following formula to increase the number of characters in a cell from 10 to 13 by inserting three zeroes after the first two characters:
=LEFT(A1,2)&"000"&RIGHT(A1,8)

I now have many more cells which vary in length from 4 to 9 characters, can the formula be modified to increase each cell using zeroes to 13 characters in the same way? i.e. one formula no matter what the current length rather than sort the cells in order of length and apply an amended formula to each.