Regex Parse

[FECL3]

Hi

I am looking to regex parse the following data:

A B C

Assets 

1 Investments 100,000 (10,000) 90,000

2 Cash 100,000 10,000 110,000

And I want the output as such

		A	B	C
	Assets
1	Investments	100,000	(10,000)	90,000
2	Cash	100,000	10,000	110,000

Are you able to assist in making this? Thanks

[apathetichell]

regex tool set to parse mode

try:

(\d+)\s+(\w+)\s([\d,]+)\s([\d\(\),]+)\s([\d,]+)

[FECL3]

Thanks, that only works if the second output has only one word and all the digits are 0

It doesn't Parse the second output if it is made up of two words or the numbers have commas in

[apathetichell]

Part 1) - I can fix that give me a second.

Part 2) yes it does. It takes commas except for the record id field.

here's a version for multiple words. Do you need .'s in your numbers? If so can you post data which more accurately reflects your data?

(\d+)\s+([a-z\s]+)\s([\d,]+)\s([\d\(\),]+)\s([\d,]+)$

[atcodedog05]

Hi @FECL3 

I am confused is this the expected output or its just me taking it literally.

Output:

Hope this helps : )

[FECL3]

For Part 1) in the example I use a one word example however it could be made up of a number of different words and in some instances they include brackets, commas, ampersands and dashes

[atcodedog05]

Hi @FECL3 

Can you provide more variant examples.

[apathetichell · Accepted solution]

This one takes in anything for your asset classification category:

(\d+\.*)\s+(\w.*)\s([\d,\.]+)\s([\d\(\)\.,]+)\s([\d\.,]+)$

it also allows .'s in your numbers.

I can't do anything else without more data and better variety.

[FECL3]

Thank you - this has worked. 😊

I have a few more variety that I would appreciate if you could build into the regex parse

Some of the lines are as follows:

2020 50,000,000 (30,000,000) 20,000,000 

Deferred Income 1,000,000 (1,000,000) 0

Best Estimate 8,000,000 8,000,000

I would like as follows

2020	50,000,000	(30,000,000)	20,000,000
Deferred Income	1,000,000	(1,000,000)	0
70	Best Estimate		8,000,000	8,000,000

Thanks in advance

[apathetichell]

Can  you mark it as a solution?

Honestly - I can't see how the current data that you posted is parseable via a regex tool. It's not a regex. It's data which is concatenated but not a regex... To transform that you really need to know what it is you're looking at in terms of columns and then you can divide it up by rows and see what fits where... I can't see how to divvy up your data above as regex... I'd stat with record id/text to columns on space. tile tool- some kind of regex-match situation where you identify which are words for the word column. a summarize tool to cocatenate and then work from numbers fitting them into categories from the group up...