[excel] How to force a newline in formula?

Case:
I want to bring together three columns of text into 1 column. For example:
A = CommentA
B = CommentB
C = CommentC

If you concatenate (=A&B&C) you get CommentACommentBCommentC. I know you can put in characters and spaces using "" inbetween the & (e.g =A&","&B&","&C), but is there a way to get excel to move the next comment onto a new line within the same cell so it looks like:
CommentA
CommentB
CommentC

Answer:
1. Try using the function CHAR to generate a newline character in your formula. It should look something like this ...
=A1&CHAR(10)&B1&CHAR(10)&C1

2. You will probably need to format the cell so that alignment has Text Wrap activated.

[EXCEL] How to show cell value in toolbar

Question: Entries in cells used to show up in a toolbar box but no longer does. I don't
know what I could have done to remove it, but I can't find a place to restore
this.

Answer: Go to menu [View] => Klik on [Formula Bar]

[EXCEL] How to count the occurrences of a number or text in a range in Excel

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*") 

 

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

[EXCEL] Removing multiple hyperlinks

Removing a bunch of hyperlinks can be tedious. Imagine the time you have to spend right-clicking on each of those links and selecting ‘remove hyperlink’ on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 hyperlinks? I’m sure that would be enough to scrape out the paint of your right-mouse button.

Below are some simple techniques you can use in order to remove single to multiple hyperlinks on a page:
Removing just one hyperlink
1. Right-Click on the cell and select Remove Hyperlink from the context menu.

Removing hyperlinks using the cell format
1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.
2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove Excel hyperlinks on selected cells the better way
1. Type in any text or number in a blank cell
2. Right-click and select Copy on the context menu.
3. While pressing CTRL, select each cell with the hyperlink you wish to be removed
4. On the Edit menu, select Paste Special.
5. Under Operation, click Multiply and then click OK.
6. If you are using Office 2007, you may need to click or change the cell format to Normal found at the Styles Group Toolbar.

Removing Excel hyperlinks using a macro
Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.
1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:


Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

4. Save your work.
5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros
6. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

Removing the Default Hyperlink Option
The most efficient way to remove them is by actually avoiding them in the first place. Now you don’t need to follow those hard and confusing steps above anymore. You can prevent Excel from formatting URLs and network links by removing its AutoFormat function at the AutoCorrect dialog box. I am using Office 2007, see the image below:

Unfortunately, if you are using Office 2003 or lower, i can’t give you the exact steps but the AutoCorrect is at Tools on the main menu bar. I just don’t remember where to go from there. If you are using Office 2000, there is no way we could prevent URLs from being formatted.