How to remove repeating character from a string

[biswajit86]

I have test cases to mark which string is invalid or valid. I used below in formula tool 

IF contains([Seller City], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"[a-zA-Z\-\.\~]+"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

After using above formula, It is showing invalid for  last one ABC DER, The output file after using formula is attached.

Please help me if there is any function or any way to achieve this

Below is the expected result

A	valid
AA	Invalid
AAA	Invalid
A AA	Invalid
A AA AAA	Invalid
A AA AAA AAAA	Invalid
A12A	Invalid
1234	Invalid
A~	Invalid
ÃB	Valid
$#%ABC	Invalid
-	Invalid
(!@#$%^&*(?) –	Invalid
A-B-C	Valid
A.B.C	Valid
A@B	Invalid
A#B	Invalid
A$B	Invalid
A%B	Invalid
A^B	Invalid
@	Invalid
#	Invalid
$	Invalid
%	Invalid
^	Invalid
	Blank
ABCD	Valid
ABC DER	Valid

Thanks

Biswajit

[mceleavey]

Hi @biswajit86 ,

It looks as if you aren't handling the spaces and double spaces in the null fields.

Try the following:

IF contains([A], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([A],"[a-zA-Z\-\.\~\s]+[^\s\s+]"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

Let me know if that helps.

M.

[afv2688]

Hello @biswajit86 ,

The formula should be like this:

IF contains([Seller City], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"[a-zA-Z\-\.\~\s]+"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

You had to add the space to be able to make it valid. This will also add as valid previous rows:

A AA

A AA AAA

A AA AAA AAAA

Which I really don't know how why you think they are different from ABD DER. You wouldn't be able to select then SAN FRANCISCO  (as an example).

Cheers

[biswajit86]

Hi,

In the expected output result below should come as invalid but after applying the logic its coming as valid records

value	remark
A AA AAA	Invalid
A AA AAA AAAA	Invalid

Thanks

Biswajit

[mceleavey]

Ok, I'm now confused.

Can you please show me what you're currently getting and what you want?

[biswajit86]

current output attached in the sheet

A AA	Seller Country Invalid
A AA AAA	Seller Country Valid
A AA AAA AAAA	Seller Country Valid

1st one is correct but hte 2nd ,3rd row is not correct . last two also need to flag as invalid.

[afv2688]

Hello @biswajit86 ,

I still don't get what you want. Could you please type the whole table as you wish it to be?

Is this how you want the results to be?

A	valid
AA	Invalid
AAA	Invalid
A AA	Invalid
A AA AAA	Invalid
A AA AAA AAAA	Invalid
A12A	Invalid
1234	Invalid
A~	Invalid
ÃB	Valid
$#%ABC	Invalid
-	Invalid
(!@#$%^&*(?) –	Invalid
A-B-C	Valid
A.B.C	Valid
A@B	Invalid
A#B	Invalid
A$B	Invalid
A%B	Invalid
A^B	Invalid
@	Invalid
#	Invalid
$	Invalid
%	Invalid
^	Invalid
	Blank
ABCD	Valid
ABC DER	Valid

If it is, could you please explain what the difference between these 2 is. Because I don't know how to differentiate them.

A AA	Invalid
ABC DER	Valid

Cheers

[mceleavey · Accepted solution]

Hi @biswajit86 ,

Give this a go:

IF isempty([Seller City]) then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"\u|\u[B]|\u\u\u\u|\u\u\u\s\u+|\u.\u.\u"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

[biswajit86]

In Test Cases tab have all the scenarios and expected results are there.

The single character also need to flag as invalid as it has no meaning.

Thanks  for your prompt help.

Thanks

Biswajit

[mceleavey · Accepted solution]

Hi @biswajit86 ,

Assuming the "Test Cases" tab is the definitive list then the following gives the correct results:

IF isempty([Seller City]) then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"\u[B]|\u\u\u\u|\u\u\u\s\u+|\u.\u.\u"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

M.