How to remove unwanted separators/delimiters from the beginning of a string

[daophuongtrinh]

Hello everyone,

I'm having some strings as follow:

, 5536463784, 5536643484

, , 5536643383, 5536644436, 5536644990

, , , 5536630664, 5536630770, 5536633864, 5536641613

, , , , , , , , , , 5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012

These strings are results of concatenating using ", " as separator. I would like to ask whether we can use RegEx or any other idea to remove the unwanted delimiters, so the output can be like this:

5536463784, 5536643484

5536643383, 5536644436, 5536644990

5536630664, 5536630770, 5536633864, 5536641613

5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012

Thank you very much

Trinh

[JosephSerpis]

Hi @daophuongtrinh you can use Regex to solve this.

REGEX_Replace([Field1], "^\,*(\d+.*)", "$1")

This will look for a comma or commas e.g. ^\,*  at the begining then it will find a digit or more aka eveything else and it will replace it with that last part.

[daophuongtrinh]

Hi @JosephSerpis 

Thank you very much for your suggestion. However the beginning delimiter is comma with space, and I have tried to add "\s" to the expression but somehow it didn't work.

Could you help take a look at the workflow I attached?

[JosephSerpis · Accepted solution]

Hi @daophuongtrinh amened the regex to work in this scenrio and also found a non-regex way to solve this as well.

[Qiu · Accepted solution]

@daophuongtrinh 
I think we can not restrict the order of comma and space.

REGEX_Replace([Field1], "^[\,\s]*(\d+.*)", "$1")

 

[DawnDuong · Accepted solution]

Hi @daophuongtrinh 

one non-regex way is to use the Text to Column tool. The way to visualize the solution is that you currently have concatenated strings which also include “blank” values which are concatenated. So you want to “decompose” them, take out the blank values, and then only concatenate the remaining non-blank values. 
this can be done in 4 steps:

1) use a RecordID tool to enable you to recombine the parts without mixing up the original strings

2) use a TextToColumn tool, but choose out put to rows and with comma as the delimiter. After this step you will have many rows than before, each row contains either a blank or a desired string.

3) filter tool: to filter out the blank. Note: do experiment with isempty() and isnull() formula to see how their behaviours differ.

4) use the summarize tool to concatenate the “broken up” parts. Do remember to group by the RecordID field so that you get back the original string combinations minus the blanks.

i did not attach a workflow, hopefully the above schematics gives you enough pointers to reach the desired outcomes.

cheers

dawn 

[daophuongtrinh]

Wow amazing :D

Thank you @JosephSerpis and @Qiu 

[daophuongtrinh]

Hi @DawnDuong 

Thank you for your idea, however the example I have here is only a column in a dataset and filtering out the blank rows would make the other numeric columns to be incorrect so I think we cannot go with this option though.

[DawnDuong]

Hi @daophuongtrinh 

You need do the first 2 steps :)

do explore the uniqueID tool and the TextToColumn Tool - these are really handy 

dawn 

[Qiu]

@daophuongtrinh 
Glad to be any help. 😁