So i have names of products in my table. Let's say this is my table:
tab1: LOAD * INLINE [ Name 'DEER WITH FUZZ, 235X60X265MM, WHITE COLOUR, 2 ASSORTED COLOURS, HANGTA', 'Glue, color.happy GOSPO', 'FIGURe LED 8,5CM AcRYL MIX WZ', 'animal SOLAR, MO', 'candle ceramic tree 6,', 'DOUCHET.3JETS+FLEX1,75M+BA,1P', 'DECOR ALU CLOTUREO 178x41,,1P', 'CAA AUTO REPARANTE 27,5'' SV,1P', 'PNEU 16X1,75 47/305 BMX,1P', 'Dog ceramic BIG', 'ribbon JUTY 6,3CM X 2,7M NAT', 'RIBBON 21CM 19,5G,' ];
I know names are strange but what I need is to find names with commas except those names where commas are surrounded with digits, for example 34,13. i want to look for names where commas (if used) are not commas showing decimal places. If there are a lot of commas and each of them has digits(so it's a number) it's okay SO:
FIGURe LED 8,5CM AcRYL MIX WZ okay, one comma with digits on its left and right
ribbon JUTY 6,3CM X 2,7M NAT okay, since both commas has digits on its left and right
RIBBON 21CM 19,5G, not okay since there is a comma which purpose isn't to show decimal places --> not surrounded with digits
animal SOLAR, MO not okay, comma not surrounded with digits Dog ceramic BIG okay, no commas
I tried something like this in chart:
=If( SubstringCount(Text(Name), ',') = 0, 'No commas', If( SubstringCount(Text(Name), ',') = 1 and IsNum(Mid(Text(Name), FindOneOf(Text(Name), ',') - 1, 1)) and IsNum(Mid(Text(Name), FindOneOf(Text(Name), ',') + 1, 1)), 'Only one comma and it has digits', 'The rest' ) ) BUT it only check for first comma, i didn't know how to write it differently i guess it can not be done in chart. How to do this in load script?
i tried doing something with
SubStringCount(Name, ',')
and
if ((isNum(Mid(Name, (Index(Name, ',') - 1), 1))) =-1 and (isNum(Mid(Name, (Index(Name, ',') + 1), 1))) = -1 , 'okay', '-')
But i don't know how to do it. Do I have to use IterNo() or what?
