Number Format (Simple)

I am sure that you have come across many times to one of the most common issues in Excel, numbers which are formatted as TEXT, therefore when you are trying to use them as a reference it won't work, so Microsoft created a simple solution using the formula VALUE.

This is a very simple and straightforward way to convert the TEXT to a Number, however and as you know Microsoft also offer the solution using the drop down when you "Hoover" on the small triangle located on the top right hand side of the cell:

But then again you need to select the full range and depending of the number of items selected it may take a longer time to convert, so and with the "VALUE" formula you can speed up the process.

To use the "VALUE" formula you just need to follow some simple steps:

1. Let's imagine that the range of numbers formatted as Text are in column C row 6.
2. Go to an empty column or Insert column next to the column where the range is.
3. Now in the column and row 6, enter =VALUE(C6)
4. Copy the formula to cover the required range.
5. Simple, your new range can now be used as a reference for any formula that you require.

 

Use INDEX and MATCH functions for table lookup

The table lists some products and from where they are originally from, and the quantity that my establishment received.

How can we look up the location from where the Product originated from? 

Normally we just create the VLOOKUP function as

“=VLOOKUP("Oranges",$A$2:$C$9,2,0)”

The result would be “Portugal” as we ask to look up “Oranges” within the table and to give the result in column 2.

Nothing wrong with this, BUT, what about if you want to know the reverse, like, from which Location is the product “Oranges”, well here is a bit more tricky as the function VLOOKUP do not look to the left, so using the INDEX AND MATCH function is better and more powerful.

So how will we do it?

For this example we use the syntax:

The MATCH function, syntax:  MATCH(lookup_value;array;match_type)

Lookup_value: is the value you want to match in lookup_array. It can be a value like =MATCH(10,A1:A15,0)or a cell reference like =MATCH(B2,A1:A5,0).
Lookup_array: is a contiguous range of cells containing possible lookup values. Lookup_array must be an array like this: =MATCH(“b”,{“a”,”b”,”c”},0) or an array reference like MATCH(7,A1:A15,0).
Match_type: is the number -1, 0, or 1 as seen here: =MATCH(B2,A1:A5,0). Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

=INDEX($A$2:$A$9,MATCH("Italy",$B$2:$B$9,0)) = “Tomatoes”, meaning:

• *        INDEX will the range of the result column in the example I want to get the “Product”
• *        MATCH is what you want to look up, in the example is the “Location”, you also need to define the range of the MATCH, the “0” represents EXACT MATCH, so it gives you exactly what you want.

There are loads more of INDEX and MATCH possibilities, I will come back to this subject in the near future. 

Convert to Number

Many times you are faced with numeric values but the format is "Text or General", although you change the format with the usual "Format Cells" sometimes the format is not changing so the VBA below will help you.

If you are extracting the integer with a formula i.e. in column A you have 
vdf01 and you enter formula - RIGHT(vdf01,2) the result will be '01' and will be formatted as "Text or General" you won't be able to get it as a Number unless you delete the "0", but if you write the formula as VALUE(RIGHT(vdf01,2)) the result will be '1' formatted as "General" but now you can change it to "Number" :-) 


Copy the code to the VBA application within excel, return to your Excel book and highlight the column (range) that you want to convert and run the macro.

Sub ConvertNumber()
'We turn off calculation and screenupdating to speed up the macro.
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    For Each xCell In Selection
        xCell.Value = xCell.Value
    Next xCell
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Bounce back with any questions,

Document Properties

Sometimes there is a need/requirement to get the Document properties in particular the "Last Saved Date", "Last Saved Time", "Last Saved By", "Author", etc...

There are various codes that can be used, however I feel that using the code below is a lot easier as only needs to be written once:

Function DocProps(prop As String)
     '-----------------------------------------------------------------
    Application.Volatile
    On Error GoTo err_value
    DocProps = ActiveWorkbook.BuiltinDocumentProperties _
    (prop)
    Exit Function
err_value:
    DocProps = CVErr(xlErrValue)
End Function

Off course that we do need now to indentify the cells with what is required from the code so in your book and in the desired cell just enter the following:

• Last Saved Date =DocProps("Last Save Time") format as dd/mm/yy
• Last Saved Time =DocProps("Last Save Time") format as hh:mm:ss
• Last Saved By =DocProps("Last Author") format as General
• Author =DocProps("Author") format as General

Excel - Date Formulas

Excel is not very user friendly when he need work with dates, however there are simple functions which allow us to work with them, see some samples below.

Please remember to adjust the cells/range references to your excel book

• Define the number of days within a month:

Let's take November 2010 as an example;

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

The result will be 30 of course...

• Get the first day of the month:

This function may be useful if you want to do a reference to a Month but have "Raw Data" with diferent days...

Let's take the date of 25 November 2012 as an example;

=(DATE(YEAR(A1),MONTH(A1),1)

The result will be 01/11/2010 and if you "custom" format to mmm-yy it will be displayed Nov-10 in your cells

Get User Name (Windows Authentication Login)

Full Exel VBA to extract the Windows Authentication login

=======================================================
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function
========================================================

Short version Exel VBA to extract the Windows Authentication login

Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function

=========================================================

I remember using Environ to get the current location of the “My Documents“folder for the current user:

MsgBox Environ("USERPROFILE") + "\My Documents"

So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was... here is what it looks like: Environ Help.
Not too useful I thought... So I decided to figure it out on my own and loop thru all the arguments possible with Environ.
Copy and run this little routine to see all that Environ offers:

MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()

Dim nCount As Integer
nCount = nCount + 1

Do Until Environ(nCount) = ""
Debug.Print Environ(nCount)
nCount = nCount + 1 Loop

End Sub

There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more... Hopefully you will find it useful and I won't forget about it again.

****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around :)

Here's a complete list (that I know of) of the named arguments for the Environ Function:
Environ arguments

ALLUSERSPROFILE
PATHEXT
APPDATA
PROCESSOR_ARCHITECTURE
AVENGINE
PROCESSOR_IDENTIFIER
CLIENTNAME
PROCESSOR_LEVEL
CommonProgramFiles
PROCESSOR_REVISION
COMPUTERNAME
ProgramFiles
ComSpec
SESSIONNAME
FP_NO_HOST_CHECK
SystemDrive
HOMEDRIVE
SystemRoot
HOMEPATH
TEMP
INCLUDE
TMP
INOCULAN
USERDOMAIN
LIB
USERNAME
LOGONSERVER
USERPROFILE
NUMBER_OF_PROCESSORS
VS71COMNTOOLS
OS
WecVersionForRosebud.FF0
Path
windir

Hi,

Here I will try to post some interesting news about MS Excel, SSRS and MS SQL. Is my aim to share with you what I have learned on this wonderfull World Wide Web, please feel free to participate and add/comment/correct any information in these pages.

Vitor da Fonseca