Split (left) a text column based on '-' or '_' as Delimiter?

[deepak91g]

I would like to achieve this task. I tried using split columns http://prntscr.com/ibe7hy

Input	Output
A_B	A
AB_C	AB
AC-D	AC
ABC-E_C	ABC
AC_E-D	AC

[gooranga1 · Accepted solution]

You could also use a formula like;

Column x = left(SUBSTITUTE(Table6[Column1],"-","_"),FIND("_", SUBSTITUTE(Table6[Column1],"-","_"),1,LEN(SUBSTITUTE(Table6[Column1],"-","_"))+1)-1)able6[Column1],"-","_"))+1)-1)

[deepak91g · Accepted solution]

@gooranga1 Thanks for suggestion and giving an idea to approach this problem.

with some changes, this worked for me

left(SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"),FIND("_", SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"),1,LEN(SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"))+1)-1)

[marc_hav]

Hi,

anyone an idea how I would go about this when one or more of the Inputs is simply A and Output should be A?

Thanks in advance!

Marc

[gooranga1 · Accepted solution]

You could also use a formula like;

Column x = left(SUBSTITUTE(Table6[Column1],"-","_"),FIND("_", SUBSTITUTE(Table6[Column1],"-","_"),1,LEN(SUBSTITUTE(Table6[Column1],"-","_"))+1)-1)able6[Column1],"-","_"))+1)-1)

[Anonymous · Accepted solution]

@deepak91g

You can also use Splitter.SplitTextByAnyDelimiter({List of delimiters},Source)

list of delimiters can be {"&","/"}  

[FOtieno]

How do I get Splitter.SplitTextByAnyDelimiter to use only the first instance of the any delimiter?

[deepak91g · Accepted solution]

@gooranga1 Thanks for suggestion and giving an idea to approach this problem.

with some changes, this worked for me

left(SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"),FIND("_", SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"),1,LEN(SUBSTITUTE(ad_word_api_report_config[client_customer_name],"-","_"))+1)-1)

[Anonymous]

@deepak91g

Hi, 

You can split column values by delimiter in Edit Queries and use split column function available. Hope this helps.

Regards,

Shruti 

[deepak91g]

I have tried using split function but it didn't  work as I have multiple delimiter to first cols