To find special character in excel cell and place a remark if found, you may use the formula below:
=IF(ISNUMBER(FIND({“!”,”@”,”’”,””””,”]”,”[“,”}”,”{“,”|”,”&”,”$”,”#”,”^”,”~”,”%”,”®”,”,”,”’”},F12)),”Found”,”None”)
Scenario (Applies to MS Excel)
When you type an entry in your worksheet that begins with any of the following prefixes, Microsoft Excel automatically creates a hyperlink:
• http://    • www.     • ftp://    • mailto:     • file://    • news:      • \\
Excel also creates a hyperlink when you type an e-mail address in the following format:Â user name@company name.com
Task
Remove the hyperlinks of the data in a range in Excel. E.g. A1:A200
Approach
Use this VBA code to remove the hyperlinks:
Sub RemoveHyperlinks()
Range(“A1:A200”).Hyperlink
End Sub
Suppose that you have a workbook that contains lists on a large number of worksheets and that you want to change the name of each worksheet to match the heading of the list on that worksheet. Not every worksheet has a list on it, but if it does, the heading is in cell B1, and if it does not, cell B1 is blank. The names of worksheets without lists should be left alone.
Ordinarily, this could be a complex task that involves looking at each worksheet to see if it has a list, copying the name if it does, clicking the worksheet tab, and then pasting in the new name. Instead of performing all of those steps manually, use Excel VBA to rename the sheets automatically.
The first step is to find out how to manipulate the particular objects that you need to work with to accomplish your task; for example, worksheets, worksheet names, cells, and cell contents. In Excel, there are at least two ways to approach the problem:
Opinions vary on which approach is preferable, but for now, try using the Macro Recorder first.
Sometimes a simple recorded macro is all you need; in these cases, you do not even have to look at the code. More often, recording alone is not enough. Instead, it is a starting point in the following process.
Begin your investigation by recording a macro that renames a worksheet to New Name. You can then use the recorded macro to develop your own macro that renames multiple worksheets based on their contents.
The code in the Visual Basic Editor should look similar to the following.
Sub RenameWorksheets()
’
’ RenameWorksheets Macro
’
’
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "New Name"
End Sub
The first four lines after the Sub line are comments. Any line that begins with an apostrophe is a comment and has no effect on what the macro does. The main uses for comments are the following:
The four comments in this recorded macro serve neither purpose, so delete them.
The next line uses the Select method to select the Sheet1 member of the Sheets collection object. In VBA code, it is not generally necessary to select objects before manipulating them, even though that is what the Macro Recorder does. In other words, this line of code is redundant, so you can delete it as well.
The last line of the recorded macro modifies the Name Property of the Sheet1 member of the Sheets collection. This is the line to keep.
After you make your changes, the recorded code should now look like the following.
Sub RenameWorksheets()
Sheets("Sheet1").Name = "New Name"
End Sub
Manually change the sheet called New Name back to Sheet1, then run the macro. The name should change back to New Name.
Now it is time to research the Sheets collection that the Macro Recorder used. The Sheets topic in the Object Model Reference includes the following text.
“The Sheets collection can contain Chart or Worksheet objects. If you need to work with sheets of only one type, see the object topic for that sheet type.”
You are only working with Worksheets, so change the code to the following.
Sub RenameWorksheets()
Worksheets("Sheet1").Name = "New Name"
End Sub
For more information about the For Each loop, see the VBA Language Reference. Click “Visual Basic Conceptual Topics”, then “Using For Each…Next Statements”. Also, be aware that the VBA Language Reference, like the Object Model Reference, will amply repay the time that you spend browsing it, and is an excellent place to look for ideas if you get stuck working on code.
Using the third example in the “Using For Each…Next Statements” topic, edit the macro so that it looks similar to the following code.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = "New Name"
Next
End Sub
myWorksheet is a variable; that is, what it represents varies. In this case, the myWorksheet variable successively represents each worksheet in the Worksheets collection. You do not have to use myWorksheet; you could use “x”, “ws”, “WorksheetToRenameAfterTheContentsOfCellB1”, or (with a few restrictions) almost any name that you want. A good rule of thumb is to use variable names that are long enough to remind you of what the variable stands for, but not so long as to clutter the code.
If you run the macro in its current state, it produces an error because Excel requires each worksheet in a workbook to have a unique name, but the following line instructs Excel to give every worksheet the same name.
myWorksheet.Name = "New Name"
To correct the line so that you can verify that the For Each loop works, change the line to the following.
myWorksheet.Name = myWorksheet.Name & "-changed"
Instead of trying to give each worksheet the same name, this line changes the current name of each worksheet (myWorksheet.Name) to the current name with “-changed” appended to it.
The macro is getting close to something that might actually solve the problem at hand. What you need now is a way to take information from the worksheets themselves—specifically from cell B1 on each worksheet—and put that information into the names of the worksheets.
This time, instead of using the Macro Recorder to find out how to refer to a cell, take a guess and see if using the Cell object will work. It is a good guess, but if you open the Object Model Reference and search for the Cell object, you find that there is no Cell object! There is a CellFormat object though.
The CellFormat object topic includes the following code in the first code sample.
’ Set the interior of cell A1 to yellow.
Range("A1").Select
It turns out that you use Range to specify a range of cells or just one individual cell. Again, you do not need the .Select portion, but you do need to find out how to refer to the contents of the Range object, as opposed to the Range object itself. If you go to the Range object topic, you can read that Range has both Methods and Properties. The contents of a Range is a thing, not an action, so it would probably be a Property. If you scan down through the list, you can see the Value property. So, try the following.
Sub RenameWorksheets()
For Each myWorksheet In Worksheets
myWorksheet.Name = myWorksheet.Range("B1").Value
Next
End Sub
You get an error if you run this on a workbook that contains worksheets where B1 is empty, because an empty Range has a Value of “” (an empty text string), which is not a legal worksheet name. It is about time to create some sample data anyway. Make the three sheets in the workbook look similar to the figure below, and then run the macro.
Source: http://msdn.microsoft.com
Now that you know something about how Microsoft Excel 2010 exposes its object model, you can try calling object methods and setting object properties. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon.
All Office 2010 applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office 2010 does not display the Developer tab by default, you must enable it using the following procedure:
After Excel displays the Developer tab, note the location of the Visual Basic, Macros, and Macro Security buttons on the tab.
Click the Macro Security button to specify which macros can run and under what conditions. Although rogue macro code can seriously damage your computer, security conditions that prevent you from running helpful macros can seriously undermine your productivity. Macro security is a complex and involved topic that you should study and understand if you work with Excel macros.
For the purposes of this article, be aware that if the Security Warning: Macros have been disabled bar appears between the ribbon and the worksheet when you open a workbook that contains a macro, you can click the Enable Content button to enable the macros.
Also, as a security measure, you cannot save a macro in the default Excel file format (.xlsx); instead, you must save the macro in a file with a special extension, .xlsm.
This following procedure shows you how to create a new blank workbook in which to store your macros. You can then save the workbook in the .xlsm format.
VBA is a full-featured programming language with a correspondingly full-featured programming environment. This article examines only those tools that you use to get started with programming, and that excludes most of the tools in the Visual Basic Editor. With this caveat, close the Properties window on the left side of the Visual Basic Editor, and ignore the two dropdown lists that appear above the code.
The Visual Basic Editor contains the following code.
Sub Hello() End Sub
Sub stands for Subroutine, which you can define for now as “macro”. Running the Hello macro runs any code that is between Sub Hello() and End Sub.
Now edit the macro so that it looks similar to the following code.
Sub Hello()
MsgBox ("Hello, world!")
End Sub
Go back to the Developer tab in Excel and click the Macros button again.
Select the Hello macro in the list that appears and then click Run to display a small message box that contains the text, “Hello, world!”
You just created and implemented custom VBA code in Excel. Click OK in the message box to close it and finish running the macro.
If the message box does not appear, check your macro security settings and restart Excel.
You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. The Office 2010 applications are created in such a way that they expose things called objects that can receive instructions. You interact with applications by sending instructions to various objects in the application. These objects are many, varied, and flexible, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do. Below are some of the important concepts that you must learn to start writing VBA Codes:
Programming objects relate to each other systematically in a hierarchy called the object model of the application. The object model roughly mirrors what you see in the user interface; for example, the Excel object model contains the Application, Workbook, Sheet, and Chart objects, among many others. The object model is a conceptual map of the application and its capabilities.
You can manipulate objects by setting their Properties and calling their Methods. Setting a property changes some quality of the object. Calling a method causes the object to perform some action. For example, the Workbook object has a Close method that closes the workbook, and an ActiveSheet property that represents the sheet that is currently active in the workbook.
Many objects come in both singular and plural versions—Workbook and Workbooks, Worksheet and Worksheets, and so on. The plural versions are called collections. Collection objects are used to perform an action on multiple items in the collection. Later on, this article discusses how to use the Worksheets collection to change the name of each worksheet in a workbook.
Source: http://msdn.microsoft.com/
RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
Syntax
RIGHT(text,num_chars)
Where:
Text is the text string containing the characters you want to extract. Num_chars specifies the number of characters you want RIGHT to extract.
Remarks
Num_chars must be greater than or equal to zero.
If num_chars is greater than the length of text, RIGHT returns all of text.
If num_chars is omitted, it is assumed to be 1.
Do It!
1. Open Excel Application
2. In Cell A1, type: Sale Price
3. In Cell B1, type: =RIGHT(A1,5)
4. Hit Enter and you should have a result as: Price
Explanation
With the above example, you extracted a String Value from Cell A1 to include all the 5 characters starting from right. Thus, from the whole string “Sale Price”, you get “Price”.