Text to columns which has both numbers and alphabets

Question

Hi, Below is the input details with header "BalancePool". It contains data separated by underscore. 3rd part after second underscore, if a number contains starting with zeros must come output without zeros and if 3rd part after second underscore, if it has number with hyphen and starting with zero must include zeros as it is. if the 3rd part contains text then the output is text. Please refer to second table below for output requirement. Please assist

Below is input detailsBalancePool PBY_BBIP_0002673854PBY_BBIP_001-035112-371PBY_BBIP_001-035112-371PBY_BBIP_IRLPBY_BBIP_IRLPBY_BBIP_IRLPBY_BBIP_IRLPBY_BBIP_IRLPBY_BBIP_IRLPBY_BBPLC_5101-04051/02

Required out put  CodeIDPreferencePBYBBIP2673854PBYBBIP001-035112-371PBYBBIP001-035112-371PBYBBIPIRLPBYBBIPIRLPBYBBIPIRLPBYBBIPIRLPBYBBIPIRLPBYBBIPIRLPBYBBPLC5101-04051/02

griffinwelsh · Answer

Yoshiro is correct switching the trim to a trimleft fixes the issue. Updated workflow is attached for you.

Solution.yxmd

Yoshiro_Fujimori · Answer

You can replace Trim function with TrimLeft function.

nukamanoj · Answer

Hi Griffin, Thanks for the quick response. The solution is serving most of the part but it is removing zeros at the end of the numeric if hyphen is not present. The requirement is to remove zeros only at the start but keep the zeros if they are at the end. The provided solution is correctly handling zeros at the start of the number by removing them for items without hyphen but it also removing zeros at the end of the number which should not be removed. Please assist.

Below is input

BalancePoolPBY_BBIP_365700PBY_BBIP_0002673854PBY_BBIP_001-035112-371PBY_BBIP_IRLPBY_BBPLC_5101-04051/02

Below is required output

123PBYBBPLC365700PBYBBIP2673854PBYBBIP001-035112-371PBYBBIPIRLPBYBBPLC5101-04051/02