Remove special characters from Huge Data

[santoshloka]

I have seen this type of characters "<>$-* and gaps between values
456.789-
*568.658
--etc
I need only values like this 425.368
rest of the special charecters need to remove

 

[Rick Rothstein]

Re: how to remove specal characters from Huge Data?

You may want to refresh this thread and look at my last message... it looks like I may have snuck in an additional edit after you read it.

Given the various anomalies I found, perhaps the best way for you to code your RegExp pattern is to replace all non-digits with a space, then trim that result and replace all spaces with a dot. Althouh numbers like this...

123.4 56

of which there are several, would then have to have the second dot removed.

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

.. or perhaps remove everything except digits and then insert a dot after the 3rd (or less eg cell A56) digit?

Cells like S89.. and 5 Q i Q^7 would just have to take their chances I think.

Still, it isn't up to us to make up the rules, the OP needs to think very carefully about all the issues raised and then clarify what they need.

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

Here aim attaching 3 files
1pdf file where i can bring the orginal levels to excel file
https://drive.google.com/open?id=0B8-usD7SI_4acVdPeUYxM1Ywblk
2)iam trying to do remove errors manually
this is another sheet iam attaching lot of errors in this ,i need to fix this
https://drive.google.com/open?id=0B8-usD7SI_4aSkI0WHM4QXpNLW8
3)after removing my errors i make my sheet like this
https://drive.google.com/open?id=0B8-usD7SI_4aWV9fVzdUcFI2RDA

i hope your doughts will clear with this documents

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

Sorry, I am not going to go through 10,000 individual cells and try to work out what you may or may not have done to "fix" them!

You need to take your time and look back over the various issues raised by Rick (mostly) and me and give us a set of logical instructions/rules that must be followed to "fix" the data. Our task would be to write some code to implement those instructions, not to make them up.

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

Here is the picture to get chanages i do manually enter because its a series ,i usually do dragging
https://drive.google.com/open?id=0B8-usD7SI_4aNWVOSzFjYlVSejQ

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

Thank you for spending time with my document..as well as giving me for valuable code i hope i can work out with your code it might be helpful.

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

@Peter_Ss there is small issue with your code your code it is working fine
in my new workbook

can we cross check if they have .decimal after 3 degits
some of them are 458568 without .decimal
if it is clear iam happy.

macro need to cross check if the value is numeric with dot
eg:456.658

 

[Rick Rothstein]

Re: how to remove specal characters from Huge Data?

Give this macro a try. What it does is remove any non-alphanumeric characters (which includes decimal points), then puts a decimal point back into the third position and then highlights in red values that are not three digit followed by a decimal point followed by three more digits. While I have no way of knowing whether that will catch every mistake that can be made in your file, it should catch the vast majority of them. What you need to do is check each red highlighted value against the original file to try and determine how to correct the erroneous value.

[table="width: 500"]
[tr]
	[td]Sub RemoveSpecialCharacters()
  Dim R As Long, C As Long, X As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "F").End(xlUp))
  For R = 1 To UBound(Data, 1)
    For C = 1 To UBound(Data, 2)
      For X = 1 To Len(Data(R, C))
        If Mid(Data(R, C), X, 1) Like "[!0-9A-Za-z]" Then Mid(Data(R, C), X) = " "
      Next
      If Data(R, C) Like "*#* *#* *#* *#*" Then
        Data(R, C) = Replace(Application.Trim(Data(R, C)), " ", ".")
      Else
        Data(R, C) = Replace(Data(R, C), " ", "")
        If Len(Data(R, C)) Then Data(R, C) = Format$(Application.Replace(Data(R, C), 4, 0, "."), "0.000")
      End If
    Next
  Next
  Application.ScreenUpdating = False
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = xlNone
  With Range("A2").Resize(UBound(Data, 1), UBound(Data, 2))
    .Interior.Color = vbRed
    .NumberFormat = "@"
    .Value = Data
    .Font.Name = "Calibri"
    .Font.Size = 12
    .Replace "???.???", "", xlWhole, , , , False, True
    .NumberFormat = "General"
    .Value = .Value
    .NumberFormat = "0.000"
    .SpecialCells(xlConstants, xlTextValues).Interior.Color = vbRed
  End With
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

awsome work ..sir
saved my tons of work load.

==>BIG THANKS FROM ME

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

Sir little more help

1)i need little modification in code
in A2:A1800 need to remove .decimal ex: 342000,342020..etc
i think whole sheet getting (.000) after 3 degits
can it be only for B:G
2)in module 2 i have made recorded code
is it possible to do it in short?
(This is for all borders)
3)I have run your code again..red color still there,which i have cleared errors already those are having still red color.
i want to show red colors only in blank cells
please find the attachment below
https://drive.google.com/open?id=0B8-usD7SI_4adE1kb0NxRnNBUlU

i hope i can get little help,sorry for keep poking