Split numbers from Text

[Learn2Excel]

Dear All,

How do we find and extract numbers from any alphanumeric text? For example - 

I have a text like : ABC123456XYZ, 234ABCD, ABCD1234. The output what I want is :

ABC123456XYZ = ABC (in col 1) 123456 (in col 2) XYZ (in col 3)

234ABCD = 234 (in col 1) ABCD (in col 2)

ABCD1234 = ABCD (in col 1) 1234 (in col 2)

I used to do it in Excel through array based formula / combination of multiple functions.

Can you please guide me the trick?

Regards

KD

[Rajukuppusamy88]

Hello,

You can use regex function to replace characters.

find the attached workflow.

Regards,

Raju. K

[Learn2Excel]

Thanks Raju, will explore this tool for other possibilities / tricks.

Meanwhile, can you please tell me if this tool offers flexibility to keep the original column as it is and populate the # in another column (the way I mentioned in the desired output demo)?

Regards,

KD

[Rajukuppusamy88]

Hi,

Check out this attached workflow.

Regards,

Raju. K

[Learn2Excel]

Thanks for your guidance Raju.

I was thinking there is a (one) specific tool to achieve this, seems there isn't.

Regards,

KD

[Jonathan-Sherman · Accepted solution]

Hi @Learn2Excel,

This is possible in one RegEx tool in parse mode with the following expression:

((?:[A-Z]+)|(?:\d+))((?:[A-Z]+)|(?:\d+))?((?:[A-Z]+)|(?:\d+))?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Learn2Excel]

Dear Jonathan,

This is exactly what I was expecting to achieve through one tool, thanks for sharing.

I was trying to look at some articles on RegEx in this community, however, RegEx looks little daunting (:)), but I am sure this tool has enormous possibilities if one can make it working.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Examples-12-Handy-Use-Cases/t...

While I will continue to explore these posts, could you please explain me the specifiers you used to construct this expression? [((?:[A-Z]+)|(?:\d+))((?:[A-Z]+)|(?:\d+))?((?:[A-Z]+)|(?:\d+))?].

If you could break it and explain, that would be really helpful for me to explore further.

Also any good reading material on this, which explains RegEx from beginning with examples and it's syntaxes, that would be very helpful too.

Thanks for your tremendous help!

Regards,

KD