While importing data from the web in Excel 2019 choosing Data>Get Data>From Other Sources>From Web, the last (trailing) zeros of numbers are being truncated resulting in the following 'Import' column:
EU
Import | Desired
968,8 | 968800
891,01 | 891010
413,47 | 413470
410,3 | 410300
43,25 | 43250
17,8 | 17800
15,05 | 15050
3,61 | 3610
6,05 | 6050
4,9 | 4900
US
Import | Desired
968.8 | 968800
891.01 | 891010
413.47 | 413470
410.3 | 410300
43.25 | 43250
17.8 | 17800
15.05 | 15050
3.61 | 3610
6.05 | 6050
4.9 | 4900
I would like to convert the data which is text (commas, periods are remaining thousands separators), to numbers like in the Desired column.
I've overdone the following working VBA function:
Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function UnTruncate(SourceVariant As Variant, _
Optional TruncateString As String = "0", _
Optional SplitSeparator As String = ",", _
Optional NumberOfDigits As Long = 3) As Long
Dim vnt As Variant ' String Array (0-based, 1-dimensional)
Dim strSource As String ' Source String
Dim strResult As String ' Resulting String
Dim strUB As String ' Upper Bound String
Dim i As Long ' String Array Elements Counter
' Convert SourceVariant to a string (Source String (strSource)).
strSource = CStr(SourceVariant)
' Check if Source String (strSource) is "" (UnTruncate = 0, by default).
If strSource = "" Then Exit Function
' Split Source String (strSource) by SplitSeparator.
vnt = Split(strSource, SplitSeparator)
' Assign the value of the last element in String Array (vnt)
' to Upper Bound String (strUB).
strUB = vnt(UBound(vnt))
' Check if there is only one element in String Array (vnt). If so,
' write its value (strUB) to Resulting String (strResult) and go to
' ProcedureSuccess.
If UBound(vnt) = 0 Then strResult = strUB: GoTo ProcedureSuccess
' Check if the length of Upper Bound String (strUB) is greater than
' NumberOfDigits. (UnTruncate = 0, by default)
If Len(strUB) > NumberOfDigits Then Exit Function
' Add the needed number of TruncateStrings to Upper Bound String.
strUB = strUB & String(NumberOfDigits - Len(strUB), TruncateString)
' Loop through the elements of String Array (vnt), from beginning
' to the element before the last, and concatenate them one after another
' to the Resulting String (strResult).
For i = 0 To UBound(vnt) - 1: strResult = strResult & vnt(i): Next
' Add Upper Bound String (strUB) to the end of Resulting String (strResult).
strResult = strResult & strUB
ProcedureSuccess:
' Convert Resulting String (strResult) to the resulting value of UnTruncate.
UnTruncate = Val(strResult)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
But I have a feeling I'm missing some important points.
I'm looking for other solutions: an improvement of my function, an Excel formula, a Power Query Solution, ... possibly when the data in Import column could be numbers or text.
