Value extraction and add to new column

Baz123

Hi,

I have a data set that has a preformatted workbook. I have managed to setup a workflow that dynamically extracts the table required, but I have some more work left to get it formatted for end use.

The table looks like the below:

ID	Compare
SHT APR >90M <150M
SHT APR <100K

What I need to do is extract the value from the ID column (the second value = upper limit in the case with a range of values as seen in the first row) and then add the value to the Compare column all the way down to the next ID so it would look like this:

ID	Compare
SHT APR >90M <150M	150,000,000
	150,000,000
SHT APR <100K	100,000
	100,000
	100,000
	100,000

I cant seem to figure out how to extract the value if there is a range, and then convert that to the numerical value.

Late edit / update - ID values are longer than shown. Should look like the above, but with extra data on end of string like this :

SHT APR >90M <150M ABC DEF GHI

Thanks.

flying008

Hi, @Baz123 

Please try this Multi-Row formula:

ToNumber(iif(IsEmpty([ID]),[Row-1:Compare],IIF(Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1) in ('M','K'),Replace(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1), Switch(Right(REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'), 1),Null(),'M','000000','K','000')), REGEX_Replace([ID], '^.*<(\d+[Mk]?).*?$', '$1'))))

BTW, if you want use simple formula, then can flow 2 steps:

Spoiler

1- 

<(\d+[Mk]?)

2- 

ToNumber(iif(IsEmpty([ID]),[Row-1:Compare],Replace([ID], Right([ID],1), Switch(Right([ID],1), Right([ID],1), 'M','000000','K','000'))))