How to remove repeating character from a string

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

Seller city validation New.xlsx

Developer

Developer Tools

Data Investigation

Accepted answers

mceleavey

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

mceleavey

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

All comments

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.

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.

current_output.xlsx

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703