Count the number of commas within a Non-Text field within Double Quotes 1

BxWill · Aug 4, 2020

On a weekly basis, I receive over 2500 non-delimited records with the following format whereby all of the data is in column A.

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?

SkipVought · Aug 4, 2020

Hi,

"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,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

BxWill · Aug 4, 2020

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.

SkipVought · Aug 4, 2020

Actually you don't have to remove COMMAS from your numeric field.

First, parse on the COLON. That gives you data like this...
[pre]
A B ...H I
equip_type "C","seller"..."Mechanical","cumulative" "4,634,000","amt"
[/pre]

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Count the number of commas within a Non-Text field within Double Quotes 1

BxWill

MIS

SkipVought

Programmer

BxWill

MIS

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor