Text to columns which has both numbers and alphabets

[nukamanoj]

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 details

BalancePool

PBY_BBIP_0002673854

PBY_BBIP_001-035112-371

PBY_BBIP_001-035112-371

PBY_BBIP_IRL

PBY_BBIP_IRL

PBY_BBIP_IRL

PBY_BBIP_IRL

PBY_BBIP_IRL

PBY_BBIP_IRL

PBY_BBPLC_5101-04051/02

Required out put
Code	ID	Preference
PBY	BBIP	2673854
PBY	BBIP	001-035112-371
PBY	BBIP	001-035112-371
PBY	BBIP	IRL
PBY	BBIP	IRL
PBY	BBIP	IRL
PBY	BBIP	IRL
PBY	BBIP	IRL
PBY	BBIP	IRL
PBY	BBPLC	5101-04051/02

[griffinwelsh]

See the attached solution. You can use an if statement in a formula tool after your text to columns parse to get the desired result.

[binuacs]

@nukamanoj using regex

[griffinwelsh]

This parses and names columns more elegantly than my solution, but does not drop the leading 0s on the Preferences with no hyphens. Are you able to do this in the Regex tool as well?

[nukamanoj]

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

BalancePool

PBY_BBIP_365700

PBY_BBIP_0002673854

PBY_BBIP_001-035112-371

PBY_BBIP_IRL

PBY_BBPLC_5101-04051/02

Below is required output

1	2	3
PBY	BBPLC	365700
PBY	BBIP	2673854
PBY	BBIP	001-035112-371
PBY	BBIP	IRL
PBY	BBPLC	5101-04051/02

[Yoshiro_Fujimori]

You can replace Trim function with TrimLeft function.

[griffinwelsh]

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