Searching with the thousand separator?

[CaptainCsaba]

Hey Everyone! I have a weird problem.

We usually get reports in PDF-s and we convert them to excel. Column A contains a lot cells with one word or a number. I need to separate the numbers that have thousand separators from the ones that don't. The problem is that after the conversion these are "number" type cells, so technically that cells that have thousand separators don't have them. the cells show 213,453,321 for example but if you click on it then it is only 213453321.

I managed to work around this with a bug (?). Basically if you do an autofilter and write the comma (,) into the Search (all) part it actually lists all the numbers with thousand separators. It does not work if you do the "text Filter" "Contains" route. My problem is that I wanted to record this to a macro but excel records it in a way that it uses too many line continuations and all the codes I know use the "Contains" part of autofilter. Is there a way to list all the funds with the thousand separators this way in VBA code?

The codes I used way like this:

[COLOR=#252C2F][FONT=Courier]Range("A:A").AutoFilter Field:=1, Criteria1:=","[/FONT][/COLOR]

 

[oldbrewer]

it may be easier to go back to original data and define how you want it presented..............

 

[Rick Rothstein]

Also I wrote that only the cells we need have a "number" type cell format. i found out that this is not the case unfortunately.

Then what are the cell formats for the numbers with commas and for the numbers without commas?

 

[CaptainCsaba]

Thanks for the help. It did not works unfortunately but I managed to find a solution. This is what a thousand separator is in VBA language: #,##0

For Each cel In SrchRng    If cel.NumberFormat = "#,##0" Then
        cel.Offset(0, 2).Value = cel.Offset(0, 0).Value
        cel.Offset(0, 0).Interior.Color = RGB(204, 255, 204)
    End If
Next cel

This code offsets the right cell by 2 to the right. thank you for the help everyone, you managed to move me in the right direction.