Word between words

[Fernando Salgueiro]

I have the attached file and I was able to break down the entire line just missing one, but since the results do not have the extra empty space I don't know how to tell excel to bring from the raw data A2 everything between B2&C2&D2 and F2&G2&H2&I2

[#2]

Fernando,

B2 =TRIM(LEFT(A2,9)) 
C2 =TRIM(MID(A2,10,11)) 
D2 =TRIM(MID(A2,22,4)) 
F2 =LEFT(B2,5) 
G2 =TRIM(MID(A2,101,3)) 
H2 =MID(A2,105,7) 
I2 =TRIM(MID(A2,118,8)) 

But I don't know why D3 and D4 should be empty.

[#3]

The original data had a lot of nonuniform empty cells, I could find formula for all the cell but one so that is the reason why I want to do a formula to get everything after the last cell and before the others 

---

@Detlef Lewin wrote:

Fernando,

B2 =TRIM(LEFT(A2,9)) 
C2 =TRIM(MID(A2,10,11)) 
D2 =TRIM(MID(A2,22,4)) 
F2 =LEFT(B2,5) 
G2 =TRIM(MID(A2,101,3)) 
H2 =MID(A2,105,7) 
I2 =TRIM(MID(A2,118,8)) 

But I don't know why D3 and D4 should be empty.

---

[#4]

I assumed you showed the desired result since there were no formulas in the workbook.

It is not clear to me what you really want. Obviously not formulas.

[#5]

Combining with previous discussion https://techcommunity.microsoft.com/t5/Formulas-and-Functions/formula-with-a-irregular-field/m-p/154... it looks like

- last 4 fields are always on fixed position;

- first field is till first space if substitute " -" on "-"; or it looks like till second space;

but the logic of rest fields depends on content, not on positions or spaces. As a guess, if last field ends by W* second field will be till first found W*, otherwise till next space.

Anyway, the logic is to be defined.