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]

why do you need to treat 123456 differently to 123,456

if 123456 is text then it is very simple but if they are both numbers they are the same value

 

[CaptainCsaba]

Because on the PDF the numbers we need have thousand separators but there are other numbers and words in the same column that we don't need. We convert it to excel and the numbers that we need keep the thousand separators. I basically need to select or copy all the cells that are numbers that have tousand separators, the numbers that don't have them are the ones that we don't need. There is nothing else to distinguish them by. Do you know another way?

 

[oldbrewer]

I spent 2 hours yesterday searching for a way - to no avail. I therefore suggest you turn your attention to the PDF - who makes it - from what data - and try to mark the wanted numbers in another way. What is the document or data set prior to the PDF ?

 

[Akuini]

the cells show 213,453,321 for example but if you click on it then it is only 213453321.

I think you can use Range.Text property in vba to get the comma.
Like this:
I use col D as helper column.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1083394a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1083394-searching-thousand-separator.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
n = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
[COLOR=Royalblue]Set[/COLOR] rng = Range([COLOR=brown]"A1:A"[/COLOR] & n)
[COLOR=Royalblue]ReDim[/COLOR] va([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n
    [COLOR=Royalblue]If[/COLOR] IsNumeric(Cells(i, [COLOR=brown]"A"[/COLOR])) [COLOR=Royalblue]And[/COLOR] InStr(Cells(i, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]Text[/COLOR], [COLOR=brown]","[/COLOR]) [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"Y"[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
Range([COLOR=brown]"D1"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
Range([COLOR=brown]"D:D"[/COLOR]).AutoFilter Field:=[COLOR=crimson]1[/COLOR], Criteria1:=[COLOR=brown]"Y"[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[CaptainCsaba]

Hey Akuini,

Thanks for the code but unfortunately it did not work, it did not see the comma as text but as part of the number and thus was empty in the end. Any ideas?

Another thought I had is that i realised that what could work is that excel for some odd reason sees the cells with the thousand separated numbers as a Number type cells and the others as a General type cell. What is the code to offset or copy the cells that are of a specific type?

 

[Rick Rothstein]

Does Akuini's code work if you change the If..Then statement inside the For..Next loop to this...

[table="width: 500"]
[tr]
	[td]If IsNumeric(Cells(i, "A")) And (Cells(i, "A").NumberFormat Like "*,*") Then va(i, 1) = "Y"[/td]
[/tr]
[/table]

 

[CaptainCsaba]

No, it still does not unfortunately. If you want to try it out a bit more Here is an example of a part of column A. I highlighted the ones we need in red. Keep in mind that these numbers technically don't have the "," in them since it is only a formatted to show thousand separators, however as you can see it is the only way to distinguish it from the other cells. (Also, empty cells are complete random and normal.)

345,678,123
LOS
333
CALIFORNIA
400

SAN
175,647
USA

NW1

746,123,564
LUXEMBOURG
REF
5687
1,765

 

[CaptainCsaba]

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.

 

[Akuini]

Don't know why it didn't work.
Try something simple first:
Select one cell that has thousand separator then run this macro.
See the result in the immediate window.
It should be: 0 & 4

Sub testComma()
Debug.Print InStr(ActiveCell.Value, ",")
Debug.Print InStr(ActiveCell.Text, ",")
End Sub