Delimit Cells in Excel with Click-and-Drag Formula-CodePudding

I have the following in Cell B1:

AB,1,CD,3,EFG,3,

I have a formula that splits the cell up by delimiter , and allows me to click and drag this horizontally to as many cells as I'd like.

The formula that allows me to click and drag, in cell C1, is the following:

=TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",LEN($B1))),(COLUMNS($C1:C1)-1)*LEN($B1) 1,LEN($B1)))

A problem that I'm running into now is that I essentially have two different delimters in Cell B6. Expected output would be the same as what's stated in Cells C1:K1, except I need an updated formula that will split by ( and )

An excel formula solution is needed -- most likely just tweaking my current formula I've provided above. No macro solutions, please.

CodePudding user response：

To use a formula for both cases, you can use this:

=LET(fixedBracket1,SUBSTITUTE($B1,"(",","),
fixedBracket2,SUBSTITUTE(fixedBracket1,")",","),
TRIM(MID(SUBSTITUTE(fixedBracket2,",",REPT(" ",LEN(fixedBracket2))),(COLUMNS($C1:C1)-1)*LEN(fixedBracket2) 1,LEN(fixedBracket2))))

It first replaces all (-Brackets with a comma, then all )-Brackets.

If you are on the Insider channel you could use TEXTSPLIT