Split text string

[Anonymous]

Hi,

I need to split my data in the format below. Remove data after dash but since the data format is different in all rows, search function isn't working. Can someone help please. 

Thanks.

Before	After
A00034	A00034
012302-001	12302
012302-002	12302
102538-001	102538
102538-002	102538
0928abc-01	0928abc
0928abc-02	0928abc
1856XYZ54	1856XYZ54

[Thim]

Go into Quiry Editor

Right click on the column and choose "split column" and "by delimiter"

Choose Custom, and put the sign -

This will put all data past the - in a new colunm, and entirely remove the -

then you can just remove the "new" column.

Hope the will help. 🙂

[Thim · Accepted solution]

If you want to keep your original Coloumn you can also use this Dax formula.

After = IFERROR(
    MID(Table1[Before];1;SEARCH("-";Table1[Before])-1);
    Table1[Before])

have a great weekend. 🙂

[Anonymous]

@Thim

Wow! That worked. 

Thanks for the help! 🙂

[Anonymous]

Need further help!

I have a database as below and need to populate my client status as new or old. A client is considered as new if the 'date' and 'live date' are same. For rows 2 and 3, client is same however it has a new deal marked as -002.  client 12303 is counted twice for data source B. I need to populate the second row as blank as it has already be counted once in row 2.

A client is considered as new if the 'date' and 'live date' are same. client 12303 is counted twice for data source B. 

Apologies for too many complications.

Data source	date	Live date	Deal ID	Client ID	Status I am getting	REQUIRED STATUS
A	26-Oct	01-Sep	A00034	A00034	old	old
B	15-Oct	15-Oct	012302-001	12302	new	new
B	26-Oct	26-Oct	012302-002	12302	new	(blank)
A	01-Sep	01-sep	102538-001	102538	new	new
A	26-Oct	26-oct	102538-002	102538	new	(blank)
B	26-Oct	01-Sep	0928abc-01	0928abc	old	old
B	26-Oct	01-Sep	0928abc-02	0928abc	old	old
A	01-Sep	01-Sep	1856XYZ54	1856XYZ54	new	new