Excel: Conditional Formatting of cells that contain question mark as text filter (use tilde question mark)

~?

If you want to color code some excel cells (conditional format) based on text value and the text value is a question mark, then use tilde question mark, otherwise it will match any character!

Excel: Hide content of cell

http://office.microsoft.com/en-001/excel-help/hide-or-display-cell-values-HP005255043.aspx

• To hide all values, click Custom in the Category list, select the existing codes in the Type box, and then type;;; (three semicolons) in the Type box.

Excel: Coloring a row based on a single cell

Use a formula:

To color the whole row if in column A is the text x

=INDIRECT("A"&ROW())="x"

=INDIREKT("A"&ZEILE())="x"

Excel: pad text column up to a certain amount of characters

Was looking into creating some formula to make sure that a concatenation of texts will always look the same on a switch.

Ended up using A1&REPT(" ", 15-LEN(A1))

This made sure that A1 always ends up being 15 characters long.

Maybe there are better ways?

Excel: copy paste of cells with multiline text show up with quotes "

Current workaround is to copy paste into word and then into any editor/terminal of your choice.



Again I have not been able to copy the cells as how they are shown, i.e. not quotes, without going through word. 

Excel: Transpose anyone? Much needed feature easily accessed

I started it all wrong with some excel table and decided I need to switch rows with columns and thought that it takes me some time to actually do it manually or find a feature in excel to do it.

Excel to the rescue: Transpose

Just select the table or rows/columns then CTRL-C, go to a new Sheet or wherever and CTRL-V, then select T or transpose icon. Done.

I just have not been able to do this without choosing another place or Sheet, i.e. no same place transpose? Anyone any idea?

Excel: row numbering

It took me more than 5 minutes to actually find the right link for my problem to number rows automatically with some correction on where to start.

=ROW()-9

If placed in A10, this will give you 1.

Here's the original article, where I found it from:

http://agsci.psu.edu/it/news/2010/11/automatically-number-rows-in-excel

Excel: All m acros for use in all excel files

• show developer ribbon
• Record Macro enter some dummy text as name and select personal workbook
• Stop Recording (you did nothing in between, right?)
• Press Visual Basic or ALT-F11
• Select Personal Workbook and Module1, click view code
• replace everything with a sub end sub, e.g. the highlight from a previous post

http://office.microsoft.com/en-us/excel-help/create-and-save-all-your-macros-in-a-single-workbook-HA102174076.aspx

Excel: filter rows based on cells that contain non-ascii characters

After running the script to highlight non-ascii characters you can use the autofilter of excel 2010 and filter on color!

http://support.microsoft.com/kb/213923

Excel: Highlight non-ASCII characters in Excel

http://www.excelforum.com/excel-new-users/821220-need-help-finding-all-non-ascii-characters-in-spreadsheet.html

Kudos to  protonLeah, many thanks. As well as for MAP-Daniel.

This is what I used:

Sub NonAscii()
    Dim UsedCells   As Range, _
        TestCell    As Range, _
        Position    As Long, _
        StrLen      As Long, _
        CharCode    As Long
   
    Set UsedCells = ActiveSheet.Range("A1").CurrentRegion
    For Each TestCell In UsedCells
        StrLen = Len(TestCell.Value)
        For Position = 1 To StrLen
            CharCode = Asc(Mid(TestCell, Position, 1))
            ' If CharCode < 32 Or (CharCode > 32 And CharCode < 48) Or (CharCode > 57 And CharCode < 65) Or (CharCode > 90 And CharCode < 97) Or CharCode > 122 Then
            If CharCode > 127 Then
                TestCell.Interior.ColorIndex = 36
                Exit For
            End If
        Next Position
    Next TestCell
End Sub

Ascii: Ascii table

http://www.asciitable.com/

Excel: Add a VBA as a macro

http://msdn.microsoft.com/en-us/library/ee814737.aspx

Excel: Data validation; how to achieve case sensitive?

I have been using data validation in excel for some worksheets where I use the columns as inputs for a formula at the end of the row.

But I was not aware that it is not case sensitive, i.e. all the entries in the second worksheet like WAN do not take effect. A user can type wan and then continue.

To achieve case sensitivity you have to actually put all possible entries into the dialog of the data-validation->source as a comma separated list.

Source    WAN,LAN,CAN,MAN...



A very good resource is also http://www.contextures.com/xldataval01.html

Excel: Hide text in column

Right click -> Format Cells -> Number -> Custom -> ;;;