Competitive and Technical Intelligence ToolBox

Sometimes in excel data, we encounter various unwanted characters, spaces etc which are not cleaned by common clean function in the excel. The following function easily clear those characters. Function sFnRemoveSC(sInput As String) As String Dim lLoop As Long, sSpecialChars As String sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`" For lLoop = 1 To Len(sSpecialChars) sInput = Replace$(sInput, Mid$(sSpecialChars, lLoop, 1), " ") Next sFnRemoveSC = sInput End Function If you by "Invisible" characters mean "not on the keyboard", then use the code number and add to the string using the character code  sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`" + Chr(9) + Chr(10) Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String Dim X As Long, CodesToClean As Variant CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _ 21, 22, 23, ...

Some times Wikipedia consists of interesting data in tabular format and we need it for further analysis of evaluation. Coping in excel through manually is time consuming, the vba code presented below can solve this issue. The red mark code can be changed to as per your need. yx denoted rows and column where data needs to extracted. url is the wikipedia url and  tblnumber is the table number (start form 0 as first table). Sub wikitable() Dim oDom As Object: Set oDom = CreateObject("htmlFile") Dim x As Long, y As Long Dim oRow As Object, oCell As Object Dim data y = 1: x = 1 url = "https://en.wikipedia.org/wiki/List_of_mobile_network_operators" tblnumber = 0 With CreateObject("msxml2.xmlhttp") .Open "GET", url, False .Send oDom.body.innerHtml = .responseText End With With oDom.GetELementsbytagNAme("table")(tblnumber) ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length) For Each oRow In .Rows For Each oCell In oRow.Cells data(x, y...