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

[atcodedog05]

@SH_94,

Yes thats correct.

[SH_94]

Dear @atcodedog05 ,

May i know if you know the formula to split the setting below:

1. ZURU??/!KUTA

2.EYRI{}:keya

Result wanted (splitting the letter and removing any sign in between)

1. ZURU (first column) KUTA (second column)

2.EYRI (first column)  keya (second column)

Thank you in advance.,

[atcodedog05]

Hi @SH_94 

Here is the regex

(\u+)[^\u]+(\u+) 

(\u+) letters

[\u+] not letters

(\u+) letters

And we are extracting the letters into 2 columns.

Workflow:

Hope this helps 🙂

[SH_94]

Dear @atcodedog05 ,

May i know why we need to add plus in this case?

Thank you.

[atcodedog05]

+ is one or more characters. In that scenario one or more non letters

[JoaoLeiteV]

@SH_94 just a quick tip, if you want to check what parts of a Regex code you can use Regexr.

You paste the expression on the upper part and then you can hover over parts of the expression and an explanation will appear. Also, in the lower part under "Tools -> Explain" you can see a whole explanation of your regex expression.

I always use this website when working with regular expressions

[SH_94]

Dear @atcodedog05 ,

I trying to use this formula for the setting below but it seem does not work:

1. HU YU*#^ JUE

Result wanted 

1. HU YU (first column) JUE (second column)

Meaning to say we need to consider the spacing as well whenever build the formula?

Thank you.

[JoaoLeiteV]

That's correct, you have to consider your spacebar.

In this case, you need to adapt @atcodedog05 regex to ([\u\s]+)[^\u]+([\u\s]+)

([\u\s]+) - Any combination of letters and spaces

[^\u]+ - Anything that's not a letter

([\u\s]+) - Any combination of letters and spaces

I tested this regex using "HU YU*#^ JUE JUE", just to make sure that if there are some spacebars in the second part, it'll also put the text in the second column.

[atcodedog05]

Hi @SH_94 

My regex would be similar to @JoaoLeiteV 🙂