Convert shortcuts to full form using Excel VBA

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

Watch the video:

Leave a Reply

Your email address will not be published. Required fields are marked *