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.
It seems you were using the Legacy Wizard rather than Power Query.
If you use Power Query, after selecting the Table, select
Transform
.Then, if the number column has been imported as text, and is showing the digits separator of the comma, don't remove the commas. Rather:
Change Type --> Using Locale
That should take care of things.
EDIT:
With regard to retaining hyperlinks from a web table using Power Query, it is not as straightforward as with the Legacy Wizard, but here is a method that seems to work with your source.
It requires three queries and a function. And you will need to edit the table after the download to format the numbers, and possible the hyperlinks.
ExcelTrim
Enter the code below into the Advanced Editor of a Blank Query
Table 0
Note I used the
Changed Type with Locale
feature which should eliminate your dropped zero's problem.getLinks
Merge1
Returns the links in a separate column from the Videos
Alternatively you can use:
Merge1 (2)
Returns a
HYPERLINK
formula to the table which provides a clickable link with a friendly name.If you use Merge1 (2) to get the hyperlinks, after saving, you will need to select the
Linked Video
column, and do aFind/Replace
or=
with=
in order to turn the formula from a text string into a formula. If you refresh the query, you will need to repeat this process.You may also want to format the
Views
andLikes
columns to show your thousands separators.Here is an example using `Merge1 (2) with the hyperlinks and my thousands separators.