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 shortform 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
