Help please! Remove a string of text with fixed format

[Lifan]

Hi,

I'm a new user and just playing with a datatable, trying to clean up a text field before publish to users.

The field is a free text in our system. However in some lines it included a system code which takes up space and no use to anyone.

The code has a consistent format, 8-4-4-4-12 5 sets of letter or number ending with ";".

Here is a example:

24f1ef97-5df5-4d3f-8976-eeca0e157432;Creative Production,Production

I've searched the forum and google, couldn't find any similar issues before.

Could anyone provide some help?

p.s. I'm using the Query function inside the Excel.

Thanks,

Lifan

[GilbertQ]

Hi there, can you give an example of what you would like the desired output to be?

[Lifan]

Hi Guavaq,

Good point, I'd just like to remove that weird code and keep the remaining info.

In that example, it should only show:

Creative Production,Production

Thanks!

Lifan

[GilbertQ]

Hi @Lifan

When using the Query Editor, I would suggest the following:

• Duplicate your column, so that you still have your original column.
• Then in the duplicated column right click and select "Split Column", then select "By Delimeter"
• When the Window Opens where it says "Select or enter delimiter" select "Custom" and then put in ";"
• 
• Then click Ok.
• Now you should see the following as shown below.
• 

[Lifan]

Hi Guavaq,

Thanks but problem is the content doesn't always have the code in there... sorry if didn't make it clear earlier.

It's like this:

24f1ef97-5df5-4d3f-8976-eeca0e157432;Creative Production,Production

ABC promotion

Production charge

245B3897-3ef5-4d9f-5184-ebe5015e7432;7/5,6/7 active

need to change to this:

Creative Production,Production

ABC promotion

Production charge

7/5,6/7 active

[GilbertQ · Accepted solution]

Hi @Lifan

No problem when you have created the additional column (Column1), when it does not find anything after the delimiter it should then have a result of null 

So then what you can do is to create a conditional column where if the Additional Column (Column1) is null then use the Original column. 

In doing that you will get the output you require.

[Phil_Seamark]

Maybe split to rows using the ";" char in Power Query and filter out any rows that contain a "-"

[Lifan]

Yes it worked! Thank you!!!!