cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deepak91g Regular Visitor
Regular Visitor

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

 

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

InputOutput
A_BA
AB_CAB
AC-DAC
ABC-E_CABC
AC_E-DAC
3 ACCEPTED SOLUTIONS

Accepted Solutions
gooranga1 Senior Member
Senior Member

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

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)

splitter.PNG

deepak91g Regular Visitor
Regular Visitor

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

@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)

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

@deepak91g

 

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

 

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

5 REPLIES 5

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

@deepak91g

 

Hi, 

 

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

Regards,

Shruti 

deepak91g Regular Visitor
Regular Visitor

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

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

gooranga1 Senior Member
Senior Member

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

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)

splitter.PNG

deepak91g Regular Visitor
Regular Visitor

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

@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)

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

@deepak91g

 

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

 

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