Extract certain characters from string

[SH_94]

Dear Community,

I would like to ask how we can build the formula that able to extract the data as per screenshot below.

I also attached the excel file for your reference.

Many thanks in advance for the assistance

[SH_94]

Hi @JoaoLeiteV ,

Thanks  a lot for the prompt response. I was trying to use the formula : ([A-Z]+)([A-Z]\d+) for the format below 

1. JASON KING HUU AU19233

2. MELVING TANG ANN AUW JU1933

Result wanted 

1. JASON KING HUU (First column) AU19233 (Second Column)

2. MELVING TANG ANN AUW (First column) JU1933 (Second Column)

But look like not working. Do you know which part that i need to amend?

Thank you.

[apathetichell]

you need to match spaces.. try something like:

regex tool in parse mode

(.*)\s(\w{2}\d+)$

and makes sure case insensitive is checked...

[SH_94]

 Dear @apathetichell ,

Could you briefly explain the meaning fn the formula used?

Thank you.

[apathetichell]

For your question of:

Thanks a lot for the prompt response. I was trying to use the formula : ([A-Z]+)([A-Z]\d+) for the format below

1. JASON KING HUU AU19233

2. MELVING TANG ANN AUW JU1933

Result wanted

1. JASON KING HUU (First column) AU19233 (Second Column)

2. MELVING TANG ANN AUW (First column) JU1933 (Second Column)

your formula wasn't matching a \s - so any spaces were treated as though your regex didn't match what it was looking for basically preventing you from getting data. My formula look for anything everywhere until a space - but not just any space - a space which is trailed by exactly 2 letters , any number of digits, and an end of the line.

[atcodedog05]

Hi @SH_94 

You can use something like this.

Workflow:

(.+)\s(\w+)

in this

(.+) first group which any characters.

\s space

(\w+) any character with no spaces. Hence the last sequence.

[apathetichell]

This workflow should work for the data you posted...  It uses formula with regex_replace...

[SH_94]

Dear @atcodedog05 ,

May i know how we can develop the formula if we have the format below:

1. 0028382AT3626

2. 8933738W67767

3.5677D65

Result wanted (splitting after capital letters)

1. 0028382 (first column) AT3626 ( second column )

2. 8933738 (first column) W67767 ( second column )

3.5677 (first column) D65 ( second column )

Thank you

[atcodedog05]

Hi @SH_94 

(\d+)(\u.+) In regex tool prase mode should work

[atcodedog05 · Accepted solution]

Hi @SH_94 ,

Here is the workflow:

The pattern in this is number letter number. Split needs to be done on first occurrence of letter hence the first group is \d (numbers) and second group \u is starting from letter . ".+" means any characters after that.

Hope this helps 🙂

[SH_94]

Dear @atcodedog05 ,

Thank you for the prompt response.

May i know for the formula (\u.+) it will capture starting from letter... No matter the behind is digit,letter or sign?

Thank you.