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?