Resolving MID function problem from table of content

[Carlos of Perth]

Hi All,

Of this extracted data below that has all been entered into one cell, I need to extract the names, i.e. "Joe Banardi".

I have tried to use a mixture of MID functions, with a mixture of SEARCH and FIND functions, without luck.

Logistics Department Joe Banardi 69722254
Research and Development Department Lilian Sanchez 87206998
Income tax Department Megan Hewitt 57065580
Sales and admin Department Sara Sinacori 97185555
Complaints Department Jasmine Richards 82705678
Commercial development Department Oscar Mendez 82505620


Thanks in advance for any help.

[FDibbins]

Hi, welcome to the forum

Is the format always the same...bunch of text and spaces, then the name, followed by numbers?

[FDibbins]

Assuming your answer will be yes to the above, try this beast...
=MID(A1,FIND("xx",SUBSTITUTE(A1," ","xx",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2),1)+1,FIND("xx",SUBSTITUTE(A1," ","xx",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("xx",SUBSTITUTE(A1," ","xx",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2))-1)

Im sure some1 will give you a more eligant suggestion

[Carlos of Perth]

Hi Ford,

Thank you.

Yep, the format is always the same.

[clabulis]

Try this:

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("Department",A1)+10,)," ",REPT(" ",99),2),99))

or, if all numbers at the end are always 8 digits long, then try this:

=SUBSTITUTE(REPLACE(A1,1,FIND("Department",A1)+10,),RIGHT(A1,9),)

[Carlos of Perth]

The numbers are all 8 digits long and so I followed you recommendation.

It worked perfectly.


Thank you