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

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

That din't tell me how to decide which one(s) to eliminate. What if the cell contained "123.2.26" or "1.2.3.4"?

there is a special characters inside the cell #-,commas,dashes,& etc.
i have to remove this special characters from cell
keep only this format 123.456,rest of them need to delete.
if yu scroll up u can find my attachment

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

if yu scroll up u can find my attachment

I looked at you attachment - that's where I got the "591.9." example.

I understand about removing special characters but "." is a little different to the other special characters. If there are 2 or more decimal points in the cell, you haven't given a 'rule' for which one to remove, you just told me what it was for one particular example.

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

I looked at you attachment - that's where I got the "591.9." example.

I understand about removing special characters but "." is a little different to the other special characters. If there are 2 or more decimal points in the cell, you haven't given a 'rule' for which one to remove, you just told me what it was for one particular example.

that data is used to find the quantitys
those are elevation cordinates(z-ELEVATION).
last decimal point need to remove

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

last decimal point need to remove

Thank you, that is the sort of thing I was after.
Unless you tell me otherwise, I will assume that any 2nd decimal point would occur at the end after the other unwanted characters have been removed.
Try this in a copy of your workbook.

Sub ClearUnwantedCharacters()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long, j As Long
  
  a = Range("A2", Range("F" & Rows.Count).End(xlUp)).Value
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "[^0-9\.]"
  For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2)
      a(i, j) = RX.Replace(a(i, j), "")
      If Right(a(i, j), 1) = "." Then a(i, j) = Left(a(i, j), Len(a(i, j)) - 1)
    Next j
  Next i
  Range("A2").Resize(UBound(a), UBound(a, 2)).Value = a
End Sub

This only deals with getting rid of the unwanted characters. For the formatting (borders, wrap text etc), I suggest that you turn on the macro recorder while you do those steps manually. That will give you some code that you can have a go at altering yourself if it needs altering.

 

[Rick Rothstein]

Re: how to remove specal characters from Huge Data?

Try this in a copy of your workbook.

  a = Range("A2", Range("F" & Rows.Count).End(xlUp).Offset(-1)).Value

I think you will need to stop one row above the last line of data as the last line contains data that looks like this...

591.120 591.257 591.302 591.600 591,397 591.6 591.227 591.481 593.440 593.707 593.440 593.667

EDIT NOTE: I assume it really is not in the OP's file, but the sample he posted had this in cell C81...

5 Q i Q^7

 

[santoshloka]

Re: how to remove specal characters from Huge Data?

@Peter_ss

your code fine working fine there is a small issue with this
after 3 degits must have .decimal some of them dont have it

 

[Rick Rothstein]

Re: how to remove specal characters from Huge Data?

I think you will need to stop one row above the last line of data as the last line contains data that looks like this...

591.120 591.257 591.302 591.600 [B][COLOR="#FF0000"]591,397 591.6[/COLOR][/B] 591.227 591.481 593.440 593.707 593.440 593.667

EDIT NOTE: I assume it really is not in the OP's file, but the sample he posted had this in cell C81...

5 Q i Q^7

Just drawing your and the OP's attention to the red highlighted value which does not have all dots between the numbers.

There is another problem with the data that the OP failed to mention. Several cells (cell F54 seems to be the first one) have a comma where the decimal place should be (for example, 595,658).

And I just spotted this value in cell C62... S89 795 (I presume the S was supposed to be a 5, but note that in addition to a comma being used for the decimal point location, apparently a space can be too.

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

I think you will need to stop one row above the last line of data as the last line contains data that looks like this...

You may well be right Rick & the OP can make that change if required. The specs were to remove spaces so I just went with that.

EDIT NOTE: I assume it really is not in the OP's file, but the sample he posted had this in cell C81...

5 Q i Q^7

Yes, I had seen that item. Again, the best I could understand was to remove everything except digits (& possible decimal point)

 

[Peter_SSs]

Re: how to remove specal characters from Huge Data?

@Peter_ss

your code fine working fine there is a small issue with this
after 3 degits must have .decimal some of them dont have it

Example cell(s) in the sample file?

Edit: I suspect Rick's post 17 has answered this?

What is the rule? Replace every "," with a "."?? Please confirm or give more detail.

 

[Rick Rothstein]

Re: how to remove specal characters from Huge Data?

Edit: I suspect Rick's post 17 has answered this?

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.