Using regex to separate data into multiple columns

[satya04]

I want to understand segment share of China but the data format is a bit different here. For instance, United States: 43,750.0 (46.2%);
China: 21,446.0 (22.6%); Other International: 29,549.0 (31.2%) is shared.

I am trying to use column F (Geographic Segments (Screen by Sum) (Details): Revenue ($USDmm, Historical rate) [LTM]) to obtain only the records where there is a reference to China, Asia, Asia Pacific, and APAC. 

I would like to have the country name China and the corresponding revenue and percentage share in 2 adjacent columns. Is this possible with the approach I have taken? I am not getting the required result. Please guide me on this.

[cmcclellan]

Hi @satya04 

Check if this helps ....

• I changed the Input tool (just to a relative path so I could open the Excel file)
• I changed the RegEx expression to extract the USD value
• Added a Formula tool to remove the comma and format the percentage value properly
• the Select tool can now change the USD field to a Double
• changed the filter to the filter the values you require.