Free Microsoft Excel 2013 Quick Reference

Alphabetize Sorting within Cell

I tried another forum and no one replied so let me try here:

I wanted to be able to sort alphabetically within cells.
Example: C1 contains "dog cat monkey". I want it to sort to say "cat dog monkey". I have like a 1000 rows of data like that, so any ideas on a formula?
Thanks!


Post your answer or comment

comments powered by Disqus
I have a file with 12,000 user records (one user per row). Within each row there is a cell that contains all of the product that each user sells. The product within each cell is listed between commas.

I need to Alphabetically sort the product names within each cell and have the data remain in that particular cell.

So as an example, in cell A12, I have something that looks like this:

Apples, Zucchini, Pumpkins, Beans, Squash, Corn

I need it to read: Apples, Beans, Corn, Pumpkins, Squash, Zucchini

Thanks

Lets say I have in column A a series of cells populated with words or phrases delimited by ;; and I want them sorted within the cell. I.e.

This:

Word ;; Another word ;; This is a phrase

Should become

Another word ;; This is a phrase ;; Word

How do you preform an alphabetical sort without formula in cells changing
position as well?

I'm trying to sort within two groups. Here's an example:

Name Number
aw 2
ad 2
ce 1
fv 1

I am trying to sort the groups (with the number 2) alphabetically. I havent
been able to figue out a macro that upon recognizing the 1 can highlight both
the rows containing it. I do have a macro for the group '2', but would really
appreciate it if someone can show me a more general one.

thanks in advance

Hi

I have lists of postcodes [zip codes] in cells - many codes together in the
same cell - , but only the first two characters. For example: CT, TN, BN,
RH, etc etc.

There are a lot of them. Could someone tell me how to get Excel to order
these alphabetically in each cell that contains them please?

Note..this is not about a single code in its own cell but multiple codes in
a single cell.

Many thanks
David

Hi and thank you in advance for any help you may give!

I am using Excel 2003 SP3 on Windows XP and am trying to modify a macro that kind of answers my issue. I've attached the workbook to further explain what I'm doing.

A friend sent me a link for a possible answer on this site which was fantastic however it didn't re-merge the Job Number and Job Name cells.

Here is the code written by Aaron Blood in 2005:

	VB:
	
 Merged_Sort_Workaround() 
    Dim MyRange As Range 
    Set MyRange = Intersect(Range("A3").CurrentRegion, Range("3:65536")) 
     
    On Error Resume Next 
    With MyRange 
        .UnMerge 
        .Resize(.Rows.Count, 1).Offset(0, 0).SpecialCells(xlBlanks).Formula = "=R[-1]C" 
        .Resize(.Rows.Count, 1).Offset(0, 1).SpecialCells(xlBlanks).Formula = "=R[-1]C" 
        .Sort Key1:=.Cells(1, 1), Key2:=.Cells(1, 2) 
        Range("VBA_RecFmt").Copy 
        .PasteSpecial Paste:=xlPasteFormats 
    End With 
     
End Sub 
Sub Reset() 
    Range("VBA_Reset").Copy Range("A3") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I need is the end result to re-merge the Job Number and Job Name so that the form is clear for those that are working on it. In the example Aaron also put a button for the action. I think I know how to do this but I'm a bit iffy.

I had thought about unmerging the 3 rows in the block of information, copying the job number and job name in 2 out of the 3 cells, making the text the same color as the cell thereby rendering them invisible but I can just see all kinds of bad stemming from that. Even if I locked it, I would still run into problems with the longer job names.

If anyone can help with this type of modification I would greatly appreciate it.

Thanks again!

~ Brenda ~

Hello all...

i am trying to copy a row a certain number of times based on the number of values within a cell. (E4:E--) and then split the values contained in that cell into unique rows. i have the following:

A B C D E F
3/20/07 ARROW INC 2065114 $10,555.24 606 4/6/07
3/18/07 NORTON 2017304 $13,206.15 715 4/6/07
3/22/07 HARKMAN 2025685 $8,127.04 167 226 452 4/6/07
3/19/07 AMEREX INC 2019933 $14,131.64 186 189 4/6/07
3/23/07 G SQUARED 2029062 $8,587.68 204 233 4/6/07

what i need is:

A B C D E F
3/20/07 ARROW INC 2065114 $10,555.24 606 4/6/07
3/18/07 NORTON 2017304 $13,206.15 715 4/6/07
3/22/07 HARKMAN 2025685 $8,127.04 167 4/6/07
3/22/07 HARKMAN 2025685 $8,127.04 226 4/6/07
3/22/07 HARKMAN 2025685 $8,127.04 452 4/6/07
3/19/07 AMEREX INC 2019933 $14,131.64 186 4/6/07
3/19/07 AMEREX INC 2019933 $14,131.64 189 4/6/07
3/23/07 G SQUARED 2029062 $8,587.68 204 4/6/07
3/23/07 G SQUARED 2029062 $8,587.68 233 4/6/07

the number of rows will vary and obviously, the number of rows will increase as the rows are copied X times to correspond to the number of values contained within cell E.

it is not showing correctly, but cell A is date, cell B is company name, cell C is PO number, cell D is price, cell E is Department numbers and cell F is end date.

Any ideas?

I appreciate your help on this as I have tried many things to no avail.
TIA
AL

Need help for macro

i have below table,code required to check which is the first string within cell value appearing first in Table & then assing no of that string (i.e TEST) in output column, how can i use Instr function or any other way i can do it, i will appreciate any help on this
TEST No
ECG 1
MER 2
BPT 3
CBC test 4
CXR test 5

Cell Values Output required
ECG,MER,BPT,CBC test,CXR 1
CBC test,MER,BPT 2
ECG,BPT, 3
for 1 output ECG is First string found within cell
for 2 output MER is First string found

I have a spreadsheet where there are multiple sections I'm trying to sort. Every section has sorted without issue except for one. I keep getting the error message that it is unable to sort merged cells. I have selected the entire section to be sorted and "unmerged" all cells but the same error message keeps coming up. I have also searched via "find" for any merged cells and there are none in this section. Any advice on how to resolve this?

Hi,

I have various values contained in cells, ranging from single values to several different combinations of values, separated by commas (ie. 17,25,50,60).

Is there any way to search cells for specific values and apply Conditional Formatting to the cells that contain those values, regardless if they're alone in the cell or with other values.

So, if my target search values are "10" 24 and 59, (as referenced in A2, B2, C2) and say cell D8 contains "5,10,33", then D8 would recieve the CF'g, since it contains the 10.

If it's possible to target specific values within cells, then I'd like to bold and apply a Text color to that number (10) only, and leave the other values s unaffected. If targeting specific values in a cell is not possible, then simply apply a color to the entire cell.

I'd really appreciate any help as to how to achieve this.
Thanks!

Sure to be a hit. Code will highlight and bold user defined text strings within cell text strings. Very nice code which can be implemented into text type macros and tools. Macro can easily be modified to perform as a function subroutine. To use it, simply set up values for range to search in (Yes, it can be a 3d range spanning multiple columns and rows). Set up your array of words to search for. I have attached a simple workbook with working example as well as included code posting. Enjoy.

http://programminglibrary.com/Progra...ext String.xls


	VB:
	
 
Sub ColorandBold() 
     'USE-COLOR AND BOLD TEXT STRINGS WITHIN TEXT EXCEL VBA
     'BROUGHT TO YOU BY WWW.PROGRAMMINGLIBRARY.COM
     'CREATED BY MARK SLOBODA
     
     '************************* DEC VARS *******************************
    Dim myCell As Range 
    Dim myRng As Range 
    Dim FirstAddress As String 
    Dim iCtr As Long 
    Dim letCtr As Long 
    Dim startrow As Long 'BEGINNING OF RANGE
    Dim endrow As Long ' END OF RANGE
    Dim startcolumn As Integer 'BEGINNING COLUMN
    Dim endcolumn As Integer 'END COLUMN
     
     '************************* SET VALUES*****************************
     'DUMMY VALUES - COULD BE PASSED
    startrow = 2 
    endrow = 5 
    startcolumn = 1 
    endcolumn = 2 
     
     'SET UP RANGE YOU ARE COLORING AND BOLDING -YOU COULD MODIFY TO PASS VALUE TO
    Set myRng = Range(Cells(startrow, startcolumn), Cells(endrow, endcolumn)) 
     
     'SET UP ARRAY WITH WORDS YOU WANT TO COLOR AND BOLD - YOU COULD PUSH VALUES FROM A LISTBOX TO THIS ARRAY
    myWords = Array("dog", "cat", "hamster") 
     
     'BEGIN MASTER LOOP---------------------------------------
    For iCtr = LBound(myWords) To UBound(myWords) 
         'ERROR FOUND-BYPASS
        On Error Resume Next 
        With myRng 
            Set myCell = .Find(What:=myWords(iCtr), After:=.Cells(1), _ 
            LookIn:=xlValues, LookAt:=xlPart, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
             'LOGIC CHECK
            If Not myCell Is Nothing Then 
                FirstAddress = myCell.Address 
                 
                Do 
                    For letCtr = 1 To Len(myCell.Value) 
                        If StrComp(Mid(myCell.Value, letCtr, _ 
                        Len(myWords(iCtr))), _ 
                        myWords(iCtr), vbTextCompare) = 0 Then 
                            myCell.Characters(Start:=letCtr, _ 
                            Length:=Len(myWords(iCtr))) _ 
                            .Font.ColorIndex = 5 
                        End If 
                         
                    Next letCtr 
                     
                    For letCtr = 1 To Len(myCell.Value) 
                        If StrComp(Mid(myCell.Value, letCtr, _ 
                        Len(myWords(iCtr))), _ 
                        myWords(iCtr), vbTextCompare) = 0 Then 
                            myCell.Characters(Start:=letCtr, _ 
                            Length:=Len(myWords(iCtr))) _ 
                            .Font.FontStyle = "Bold" 
                        End If 
                    Next letCtr 
                     
                     'GET NEXT ADDRESS
                    Set myCell = .FindNext(myCell) 
                     
                Loop While Not myCell Is Nothing _ 
                And myCell.Address  FirstAddress 
            End If 
        End With 
    Next iCtr 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've searched through the forum reading all about the problems with sorting merged cells. Unfortunately I did not know that before I created this huge spreadsheet and entered in all the information. Now I have spent all this time on it, I find that I cant sort if I have merged cells. I was wondering if there was any way possible using VBA for this particular case. I've attached a sample of what the spreadsheet is like, but on a much smaller scale. Any ideas would be greatly appreciated.

Thanks!

Is there a function that can read the letters that are in a cell from the worksheet in VBA?

For example, I input the alphabets: ABCD in cell A1 on the worksheet. In the VBA macro can is there a function that I can use to do the following:

- Open a text file that has it's last character in the file name that consists one of the letters in cell A1, e.g. A, B, C or D. The text file name could be X001A.txt.

- Therefore, I need something like: If Right(filename, 1) = Function(contents of cell A1) then ....

Hope it's clear and would appreciate any help. Cheers...

E

Hi Everyone!

I am using Excel 2003 and there is no feature to sort highlighted cell in a column and I know that it exist on Excel 2007. I would like to have a macro to sort highlighted cell in column B.

I appreciate your help and thank you in advance.

RNF

Im sorting a dynamic range as mentioned in this post. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0,COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).


	VB:
	
 Sheet1.Range("drWarningTypes") 
    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _ 
    MatchCase:=False, Orientation:=xlTopToBottom 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why is this happening?

Is it possible to automatically find or sort on cells with ALL CAPS content?

I have a data source in Excel which differentiates content by either Proper Text or ALL CAPS. I need to count each and measure proportions of data based on text.

Any help that can be provided would be greatly appreciated.

Thank you

DBT

How can I sort by cell backgroung color?

Hi,

I am desparately trying to sort merged cells (many columns, many lines) without having to unmerge them.

Is there any way ?

Is there any way to alphabetically sort my sheets? I am trying to create an efficient way to look up different machine types. I would like to know if I needed to add a sheet in the future that I can just post it and have it resort alphabetically.

Thanks,
Dan

We're seeing an issue in Excel 2007 related to sorting data using the sort
within an auto filter. Excel behaves differently if you run the sort from
the filter drop-down or if you run it from the Ribbon. From the Ribbon, the
sort function works every time for all columns. From the drop-down, Excel
only sorts the filtered columns.

This behavior is different than 2003 where Excel sorts all of the columns
regardless of how many columns have filters. This is a problem since there
are manyspreadsheets created in earlier versions of Excel with filters on
only some columns and there is no warning that the data will become
inconsistent. Any assistance is greatly appreciated.

Below are the steps to recreate the problem.

1. Create a blank spreadsheet in Excel 2007.
2. Populate the first row with column headers.
3. Populate a few rows worth of data.
4. Select some, but not all, columns and click the Filter button on the
Ribbon. This applies filters to the columns you select, not all columns.
5. Use the sort feature in the drop-down menu in the column header to sort
the data.
6. No warning is given. Only filtered rows sort. Your table now has
inconsistent data

.

Make a dynamic alphabetically sorted UNIQUE list

Is it possible to generate a unique list of data from a given column on SHEET1 to a different sheet, SHEET2 and sort it alphabetically with a dynamic formula?

I have SHEET1

In the first column I keep adding data every week (approximate 8000- 9000 records)

Tom
Henry
Robert
Jack
David
David
Tom
Henry
Jack

In SHEET2 I need to generate a list of all the unique records in column A from SHEET1 sorted alphabetically

David
Henry
Jack
Robert
Tom

I would love to know if this is possible WITHOUT USING A MACRO

Because the comp I need to use this sheet on either will have LOW SECURITY by default or this will have to run in STAR OFFICE which usually screws up excel macros

But if not possible I will accept a macro

The unique list must be auto updated the moment new data is added to SHEET1

Is this possible???

Regards,
Hyperdreamz

.

Hi everyone. I just wanted to ask if anyone knows of a shortcut for editing within cells but at the beginning of the cell. F2 does but starts at the end of the cell contents.

Also, F4 makes a cell absolute. But are there any shortcuts so that only the row is made absolute, and only the column is made absolute?

Thanks, any help would be much appreciated.

(Note to mods: I'm not sure if I posted this already. I did a search and nothing came up. I apologize if I already have.)

Hi there,

i have a problem to sort merged cells in the file attached. i tried via macro but could not reached a good solution. I have 30 plans inside the sheet and even manually is too hard to do it because i use some "=" to not type again every number since some are the same but when i try to sort the lines became diferent values.

more explanation inside the file.

Tks for your attention.

Dear Expert,

How to use functions to remove the first 4 Alphabets in a cell.
Example:
0026 Amt of sunlight in living room
0026 Any toilet fixtures
0003 A123
0005 Any environmental changes each season

The first 4 characters must be Numbers.

I just hope to remove these 4 numbers Characters (maybe 5 including the space) and need the rest of the information in a cell.
Thanks

Elton


No luck finding an answer? You could always try Google.