Correct Regex Expression

Hello,

I am seeking help as to the correct regex expression for this: I want to separate the account code and the account description in to 2 columns. In the pic below, the account codes are in the beginning of the cell. I made a regex expression in the 2nd screenshot below but it only captures the cell with account codes.

My problem below is that the account names which have no codes are returned as null. E.g. for uncategorized expenditure, it must be wholly parsed to account description even if the account code column is blank.

Thank you.

Workflow

Accepted answers

Jean-Balteryx

Hi @Gamaliel ,

Could you share a sample of your data ? Looking at your screenshot I'd say the following regex would do the job but not sure :

(\d+)?\s?(.*)

atcodedog05

Hi @Gamaliel

2 possible options

1. Use data cleanse tool before regex to remove leading and trailing spaces.

2. Use regex like below which handles leading spaces.

\s*(\d+)?\s?(.*)

Workflow:

Hope this helps : )

Regex (3).yxmd

All comments

bensilv

Hi @Gamaliel,

From looking at your issue, I would say a simple way forward is to add a formula on top to say:

IF ISNULL([Account Description]) THEN [Account Name] ELSE [Account Description]

Alternatively, you can utilise the zero or more option around the entire group for digits, which is represented by an asterisk. I would test the logic on https://regex101.com/ to see if it highlights the rule correctly.

Jean-Balteryx

Hi @Gamaliel ,

Could you share a sample of your data ? Looking at your screenshot I'd say the following regex would do the job but not sure :

(\d+)?\s?(.*)

Gamaliel

Please see attached sample data. Thanks.

Regex.yxmd

Gamaliel

This one works 🙂 Thanks.

Jean-Balteryx

Here it is with my regex !

Regex.yxmd

Gamaliel

Hello @jean , I am sending you another workflow. It is just that there is another problem discovered. Please see attached workflow. I added a space in the beginning of the first cell. But the correct regex does not work for it.

Thanks.

Regex.yxmd

Jean-Balteryx

Hi @Gamaliel ,

@atcodedog05 gave you the answer. I'd rather go for the cleansing solution as it makes your data cleaner and more consistent, also simplifying your regex.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703