Count the number of commas within a Non-Text field within Double Quotes
Count the number of commas within a Non-Text field within Double Quotes
(OP)
On a weekly basis, I receive over 2500 non-delimited records with the following format whereby all of the data is in column A.
My ultimate objective is to parse the data into separate columns. Maybe initially by using a comma delimiter.
However, the immediate objective is to count the number of commas within numbers that are enclosed by quotation marks. For example, for the field "cumulative", there is "4,634,000." If there is 1 or more commas within this numeric field, I will move the records to the bottom of the data set.
I am attempting to create a formula to display "2" as this is the number of columns within the field "cumulative."
Of course, one may ask if there are other numeric fields or if there are alpha-numeric fields. Per my review, there appears to only be one field that have multiple commas - the field "cumulative."
Over the last hour or so, I have not been successful in creating a formula to do this.
Using the formula below, I can count the number of commas within a cell
But, I want to only count the commas that are within numbers that are enclosed by quotation marks not commas that are within text that are enclosed by quotation marks.
Any insight as to how I can accomplish the objective?
CODE
equip_type:"C","seller":"SampleSeller LLC","buyer":"SampleBuyerCorp","address":"1000 Main St.","City":"Baltimore","State":"MD","Zip":"21201","equip_desc":"Mechanical","cumulative":"4,634,000","amt":"3892500","sell_date":"2020-02-07","id":"4674128"},
My ultimate objective is to parse the data into separate columns. Maybe initially by using a comma delimiter.
However, the immediate objective is to count the number of commas within numbers that are enclosed by quotation marks. For example, for the field "cumulative", there is "4,634,000." If there is 1 or more commas within this numeric field, I will move the records to the bottom of the data set.
I am attempting to create a formula to display "2" as this is the number of columns within the field "cumulative."
Of course, one may ask if there are other numeric fields or if there are alpha-numeric fields. Per my review, there appears to only be one field that have multiple commas - the field "cumulative."
Over the last hour or so, I have not been successful in creating a formula to do this.
Using the formula below, I can count the number of commas within a cell
CODE
=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))
But, I want to only count the commas that are within numbers that are enclosed by quotation marks not commas that are within text that are enclosed by quotation marks.
Any insight as to how I can accomplish the objective?
Talk To Other Members
RE: Count the number of commas within a Non-Text field within Double Quotes
"I receive over 2500 non-delimited records"
...yet the record example IS delimited.
???
It's just that the field COMMA delimiter has within it another delimiter: a COLON.
So is the "cumulative":"4,634,000" field, of the "over 2500 non-delimited records" the only field that is "numbers that are enclosed by quotation marks"?
Looks to me that the field name is cumulative and the field value is 4634000. Yes?
Of what significance is the number of COMMAS?
Are there other examples that have different formats other than COLON delimiters within COMMA delimiters?
What's the reason to "move the records to the bottom of the data set?"
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Count the number of commas within a Non-Text field within Double Quotes
I misspoke. The data is delimited but it was not parsed.
After some thought, I decided to resolve the immediate objective from a different angle.
I just compared the number of commas within each record with the number of colons. If there is a difference, then I know
that there is a field that contains commas.
The reason why I move the records that have a comma within a field is to flag all records that I need to parse differently.
RE: Count the number of commas within a Non-Text field within Double Quotes
First, parse on the COLON. That gives you data like this...
Then I wrote some code to put the field names like equip_type, seller, cumulative, amt, for instance, in row 1 and the corresponding data under each heading.
When 4,634,000 is put in a cell, the commas disappear. You might see them DISPLAYED, but that's all it is: a display, that you can change with numeric FORMAT.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein