×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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.

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?

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)


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

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close