Shared ramblings/ findings

Tuesday, January 24, 2012

How to separate alphanumeric in cell in Excell( just get the number)

How to separate alphanumeric in cell in Excell
Microsoft Excel offers the user a wide array of options for creating documents to hold data for timetables, financial sheets and many other types of information data sheets. Within these data sheets, a user can also assign functions to make math, financial or basic data entry tasks easier. The program also features customizable User-Defined Functions (UDF). These functions can be created using simple codes and will enable a user to accomplish a task that may otherwise be tedious to perform manually.


o    1
Open Excel by clicking the "Start" button at the bottom right-hand corner of your desktop and selecting the "Microsoft Office" folder. Inside the folder, click "Microsoft Excel."
o    2
Open the Visual Basic for Application (VBA) window by holding "Alt+F11" on your keyboard.
o    3
Type a code into the window labeled "Book 1 -- Module 1 (Code)." There are many codes that can be input. Some are more complex than others. See Resources for a guide on how to create codes. For now, here is a simple code that will allow you to view the URL used in links throughout your table:
Function OnlyNumber(xstrIn)

Dim i As Integer
Dim strReturn As String

For i = 1 To Len(xstrIn)
If IsNumeric(Mid(xstrIn, i, 1)) Then strReturn = strReturn &
Mid(xstrIn, i, 1)

OnlyNumber = CLng(strReturn)

End Function

Just copy/paste this code into the "Book 1 -- Module 1 (Code)" window.
o    4
Hold the "Alt+Q" keys to close the VBA window.
o    5
Hold the "Shift+F3" keys while in your Excel workbook window to open the "Insert Function" window.
o    6
Click on the drop-down menu next to the phrase "Or select a category," and choose "User Defined" from the list of available options.
o    7
Select "LinkAddress" from the area underneath the phrase "Select a function." The function you created will be added to the first cell that is selected on your workbook.

No comments: