Microsoft Excel Tricks: Remove Unit from Cell Values

Today, a friend asks me how to remove the unit from a column in Excel. The format of a column is like this 123.45 unit. The easiest way is to write a short VBA (Visual Basic For Application), which is supported in all Microsoft Office Products (Excel, Word, Access …)

The last few characters after space is assumed the unit. Assume we know the unit string to remove, we can simply put formula in another column.

The IF() function takes three parameters. The first is an boolean expression (evaluated to either true of false), the second parameter is the expression to set when it is true and the third parameter is the expression to set when the first expression is evaluated to false.

So the above one line VBA code is actually equivalent to the following.

1
2
3
4
5

IF Upper(Right(D2, 3)) = "DBM" Then
    E2 = Mid(D2, 1, Len(D2) - 3) ' Remove Unit
Else
    E2 = D2 'No valid Unit found
End If

IF Upper(Right(D2, 3)) = "DBM" Then
    E2 = Mid(D2, 1, Len(D2) - 3) ' Remove Unit
Else
    E2 = D2 'No valid Unit found
End If

The Upper converts the string to its uppercase. The Len returns the number of characters for a string. The Mid returns substring of a string given its start and length.

This shows that the new column simply contains the numerical results.

This example shows you that the VBA is very powerful and convenient in Excel.

–EOF (The Ultimate Computing & Technology Blog) —