Convert shortcuts to full form using Excel VBA

Home More Videos






A website visitor had the following query:
'Is it possible to enter item quantities in Excel in a short-form like 2d (2 dozens) or 2.2k (two thousand two hundred) and have it displayed as 24 and 2200?
Thanks for your help in advance!
Tony '

The macro code solution attached to a command button is given below and discussed in the  training video below:

Private Sub CommandButton1_Click()
Dim x As Long
Dim i
Dim shortText As String
x = 2
Do While Cells(x, 2) <> ""
For i = 1 To Len(Cells(x, 2))
If Not IsNumeric(Mid(Cells(x, 2), i, 1)) And Mid(Cells(x, 2), i, 1) <> "." Then
shortText = shortText & Mid(Cells(x, 2), i, 1)
End If
Next
If UCase(shortText) = "H" Then
Cells(x, 2).Value = Val(Left(Cells(x, 2), Len(Cells(x, 2)) - 1)) * 100
ElseIf UCase(shortText) = "D" Then
Cells(x, 2).Value = Val(Left(Cells(x, 2), Len(Cells(x, 2)) - 1)) * 12
ElseIf UCase(shortText) = "K" Then
Cells(x, 2).Value = Val(Left(Cells(x, 2), Len(Cells(x, 2)) - 1)) * 1000
ElseIf UCase(shortText) = "HK" Then
Cells(x, 2).Value = Val(Left(Cells(x, 2), Len(Cells(x, 2)) - 2)) * 100000
End If
shortText = ""
x = x + 1
Loop
End Sub

  • First we define the used variables.
  • Next we extract the text portion from the data entered by the user in short-form
  • Then we convert the short form into a number that can be understood by everyone keeping in mind that the user can enter the text in the uppercase also
  • Finally we loop through all the rows of the data
  • The conversion from the shortcut to the full form is completely automated