Forum Discussion
Help please! Remove a string of text with fixed format
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
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.
7 Replies
- GilbertQ
Super User
Hi there, can you give an example of what you would like the desired output to be?
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.
