Forum Discussion

Copper Contributor

Feb 16, 2018

Word between words

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 da...

Book10.xlsx10 KB

excel

Formulas and Functions

Macros and VBA

Fernando Salgueiro

Copper Contributor

Feb 16, 2018

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.

Detlef_Lewin

Silver Contributor

Feb 16, 2018

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.

SergeiBaklan
Diamond Contributor
Feb 17, 2018
Combining with previous discussion https://techcommunity.microsoft.com/t5/Formulas-and-Functions/formula-with-a-irregular-field/m-p/154888#M3401 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.