How to eliminate extra elements from a string using vb.net-CodePudding

When I read an excel I may get the unicode block/ replacement character, line feed, carriage return,pilcrow,tab , whitespace in my reading string. How to eliminate them all and have only the pure value as final output with minimal line of code.

    Dim str as string() = "53412437�  "
    O/p - 53412437

Tried replace - str.tostring().Replace("�","").StartTrim.EndTrim.

CodePudding user response：

I have found it convenient to define a function to clean up Excel strings when read from cells. I'm not sure what your character is, but it is likely the same as those I get. See if this works for you

Dim fixRangeString = Function(s As String) s.
    Replace(Environment.NewLine, "").
    Replace(Chr(10), "").
    Replace(Chr(13), "").
    Replace(vbTab, "").Trim()

xlRange = DirectCast(xlSheet.Cells(row, col), Excel.Range)
If xlRange.Value IsNot Nothing Then
    Dim rangeString = fixRangeString(xlRange.Value.ToString())

CodePudding user response：

There's a lot more junk that can be in an Excel file if you want to get rid of anything that can get in the way of processing the data numerically. Thus, to remove everything, I will invoke the following syntax, where I loop through each cell value in the range used.

The following regex filter will remove everything, but I don't use it:

'Dim dropspecchar As New Regex("[`~!@#$%^&*()_ ={}\[\]\\|:;""'<>,.?/-]") 'period removed

Instead, I use the opposite and only keep characters and numbers, as the regex filter below will remove everything except characters a,b,...z,A,B,...,Z and digits 0,1,2,3,4,5,6,7,8,9

[assume the used range inside the Excel table has 100 (0-99) rows and 100 (0-99) cols]

Dim dropbadfilechars As New Regex("[^a-zA-Z0-9]")

Dim x(99,99) As Object

'Fill array x(,) with contents of Excel cell values

For row = 0 to 99
  For col = 0 To 99
    If x(row,col)= " " Then x(row,col)= Nothing
    If x(row,col)= "." Then x(row,col)= Nothing
    If x(row,col)= "-" Then x(row,col)= Nothing
    If x(row,col)= "#VALUE" Then x(row,col)= Nothing
    If x(row,col)= "#DIV/0" Then x(row,col)= Nothing
    If x(row,col)= "#NAME?" Then x(row,col)= Nothing
    If x(row,col)= "#N/A" Then x(row,col)= Nothing
    If x(row,col)= "#REF!" Then x(row,col)= Nothing
    If x(row,col)= "#NUM!" Then x(row,col)= Nothing
    If x(row,col)= "#NULL!" Then x(row,col)= Nothing
    If x(row,col) IsNot Nothing Then x(row,col)= dropbadfilechars.Replace(x(row,col), "")
  Next
Next

Lastly, if you save an Excel as tab-delimited text (.txt) or comma-delimited (.csv), then any math functions will be eliminated during the save, but garbage like "#Ref!" will be leftover -- so they'll still need to be filtered out.