Split text with Right,Mid,Left with multiple Middle variables

[Berra2k]

So I need to separate a text string into different variables. I can do the Right and Left portions and even have managed the first Mid portion but the others are extremely hard for me.

The uploaded picture shows what the text looks like and how it needs to be split. The text is in cell A2. I need to use formulas (no text to columns) to do this.

Any help greatly appreciated.

 

[NARAYANK991]

Hi ,

Can you either explain the logic for each column text , or give at least another 10/20 rows of data so that we get an idea.

Narayan

 

[Berra2k]

So the data comes in the form of A2 from the picture - B2, C2, D2, E2, and F2 need to be filled in using formulas as they are blank. So this one was done manually. For B2 you could do a formula like =LEFT(A2,FIND("_",A2)-1).

But in the middle it gets tricky using the Mid formula, because in this example there are multiple middle strings to pull.

So essentially I need to take data from A2 and separate it using left, mid, and right, along with the FIND function so it looks like it does in this example.

 

[Hui]

B2: =LEFT(A2,FIND("_",A2)-1)
C2: =MID(A2,FIND("_",A2)+1,FIND("_",A2,LEN(B2)+2)-FIND("_",A2)-1)
D2: =MID(A2,FIND("_",A2,LEN(B2&" "&C2))+1,FIND("_",A2,LEN(B2&" "&C2)+2)-FIND("_",A2,LEN(B2&" "&C2))-1)
E2: =MID(A2,FIND("_",A2,LEN(B2&" "&C2&" "&D2))+1,FIND(" [",A2)-LEN(B2&" "&C2&" "&D2)-2)
F2: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)

or see attached file:

 

[shrivallabha]

In cell B2:
=TRIM(MID(SUBSTITUTE("_"&SUBSTITUTE($A2," [","_["),"_",REPT(" ",99)),COLUMNS($B2:B2)*99,99))
And then copy the formula down and across.