How to remove repeating character from a string
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.