Correct Regex Expression

[Gamaliel]

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.

[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 · Accepted solution]

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.

[Gamaliel]

This one works 🙂 Thanks.

[Jean-Balteryx]

Here it is with my regex !

[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.

[atcodedog05 · Accepted solution]

Hi @Gamaliel 

2 possible options

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

or

2. Use regex like below which handles leading spaces.

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

Workflow:

Hope this helps : )

[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.