Check each delimited value to make sure it meets criteria

[philwojo]

Hello, I have a workbook that has several sheets that have data on them in the following format:

107.75|106.68|96.98

The data can be a single value, or pipe delimited and contain up to 99 instances of values. Is there an easy way to check that each value has the trailing 2 digits after the decimal place? The problem is really only when the number would end in .00, like 107.00, sometimes the trailing zero's are left off. I would like to make sure that each value in the cell contains the trailing zero's.

If there is any value in the cell that does not contain the 2 numbers after the decimal point I would like to highlight the cell red and turn the font bold and yellow.

Thanks for any assistance.

Phil

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

Thanks Rick, I knew it would be that, I just didn't know the format to be used.

I tried your suggestion in my code and it doesn't seem to catch missing leading zero's with that code.

Can you copy/paste (do not type it) the contents of the cell entry that it does not work for.

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

.19 Copy and pasted from the cell that it is checking, but here is the original data that cell info comes from: [TABLE="width: 209"] <tbody>[TR] [TD="class: xl65, width: 209"]0208689,,,,,Activate,AP001,,Single-Use Account,,Add,,,2018-01-05 23:59:00,2018-02-04 23:59:00,,,0.00,0.00,USD,,,,,,,111442,ADD,.19,1,EACH,,,,,,,,,,Add,950,,ANCIRA WINTON CHEVROLET INC,Add,lgoins@ancira.com,,,,,,CHECK NUMBER,Add,EB000856,,,[/TD] [/TR] </tbody>[/TABLE]

<tbody>
</tbody>

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

.19 Copy and pasted from the cell that it is checking, but here is the original data that cell info comes from:

<tbody>

Where is the $ sign you said would be there? How is this value getting into the cell?

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

My apologies, there is not a dollar sign, I just was typing that up quickly and put it in by habit. The data will never have anything but numbers, the decimal point, and if a negative value then surrounded by parenthesis.

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

My apologies, there is not a dollar sign, I just was typing that up quickly and put it in by habit. The data will never have anything but numbers, the decimal point, and if a negative value then surrounded by parenthesis.

Try using this Like comparison instead of the one I posted earlier...

("X" & S(X) Like "*[!0].#*")

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

That doesn't seem to work, that now makes almost every value for other data sets, outside of the same I posted, trigger and turn the cell Red with the Yellow font.

.19 gets flagged as it should.
229.30 gets flagged, but it shouldn't.
110.96 doesn't get flagged.
393.91 gets flagged.

Seems like any valued that has a zero in it doesn't get flagged, everything else does.

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

Sorry anything with a zero no at the start or end of the value.

So 73.40 gets flagged, but 160.19 does not.

110.96 does not
41.00 does

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

Sorry anything with a zero no at the start or end of the value.

So 73.40 gets flagged, but 160.19 does not.

110.96 does not
41.00 does

Sorry, maybe this will work correctly...

("X" & S(X) Like "*[!0-9].#*")

 

[philwojo]

Re: How to check each delimited value to make sure it meets criteria

No apologize necessary, and this appears to be working, thanks yet again Rick!!

So, any way to work on the 2nd part of my new request, to ignore properly formatted values surrounded by (), as in (310.19) or (.19) or (43.00)?

Again I get what you are doing at a high level with the OR'ed statements, I just really don't know the logic rules to add or exclude that. I can follow a few of them, but not all of those rules, sorry.

Phil

 

[Rick Rothstein]

Re: How to check each delimited value to make sure it meets criteria

So, any way to work on the 2nd part of my new request, to ignore properly formatted values surrounded by (), as in (310.19) or (.19) or (43.00)?

The red highlighted value is considered okay even though there is no 0 in front of decimal point?