How to flag Company name with funny character and only single word ?

[Anonymous]

Hi All

I have below raw data :-

Company

@abc

#123

SMC

ADL

TDS Technology Pte Ltd

My Expected result :-

Company,Flag

@abc,Funny Charater

#123,Funny Charater

SMC,Signle word Company name

ADL,Signle word Company name

TDS Technology Pte Ltd,Not Issue

Hope some one can advise me.

Paul Yeo

[Fowmy · Accepted solution]

Thanks, @HotChilli  for your clarification and kind words  🙂

@Anonymous 

You need to have two tables, one for the Signs @,# etc and the other for the single word company names :

Then, you will and thy following column to your table to have the desired results.

Flag = 
VAR _SignFlag = LEFT([Company],1) IN VALUES(SignsList[Sign]) 
VAR _Space = CONTAINSSTRING(TRIM([Company])," ")

VAR _SingleWord =   NOT(_Space) && NOT(_SignFlag)
VAR _Sign = NOT(_Space) && _SignFlag
VAR _FullName = _Space && NOT(_SignFlag)

RETURN

SWITCH( TRUE(),
    _Sign , LOOKUPVALUE(SignsList[Character],SignsList[Sign],LEFT([Company],1)),
    _SingleWord , LOOKUPVALUE(CompanyList[Full Name],CompanyList[Word],[Company]),
    _FullName, [Company]
)

You can download the file: HERE

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Wow you are the expert . Thank 

[Fowmy]

@Anonymous 

If you have a handful of companies, you can try the Window emojis by adding a column ( WIN + .  ) like

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[HotChilli]

@Fowmy    you are a great contributor to the forum.  I loved your answer on this one, however I think the OP is trying to flag up if a "funny character" is contained already in the string.

@Anonymous  you are going to have to define a list of funny characters (or make a rule that anything that is not a letter or number is a "funny character").  I've had a quick play in Power Query by adding a column like this

if not List.ContainsAny(Text.ToList([Column1]), {"a".."b"}) then 1 else 0)

so it could be something similar