How to extract names from a text string?

Have you a list of the names somewhere in the sheet? or you want Excel to "understand" which are the names in the string?

if you had a list of names that could occur then yes or if you had some way of showing where the names are in the text (some mark like ; or something where the name starts (and preferably stops)) you can do it otherwise you cant tell excel to just pick some random words out from random locations in a string. how do you get these strings and can they be amended at data entry level

Here, try this:

Formula:

Please Login or Register  to view this content.

Sure.
But it will retun Mona ref from Avs.ref 3232 . Fra Mona ref 62

Formula:

Please Login or Register  to view this content.

You can use Evaluate formula na try to follow steps:

First take (for example) a string Faktura Fra Mona Mona lug 123 and use word from 4th place after FRA (SEARCH("Fra ";A3&"Fra ")+4) onwards (255)

• MID(A3;SEARCH("Fra ";A3&"Fra ")+4;255)
• MID(Faktura Fra Mona Mona lug 123;SEARCH("Fra ";Faktura Fra Mona Mona lug 123&"Fra ")+4;255)
• MID(Faktura Fra Mona Mona lug 123;13;255)
• Mona Mona lug 123

Now replace SECOND space with 255 spaces to get huge gape between second and third word

• SUBSTITUTE(MID(A3;SEARCH("Fra ";A3&"Fra ")+4;255);" ";REPT(" ";255);2)
• SUBSTITUTE(Mona Mona lug 123;" ";REPT(" ";255);2)
• Mona Mona_______________spaces_________lug 123

Now take left 100 characters (it should be enough for two words but won't take thirw word which is 255 chars away. You can change those 100 to 200 for example if first two words are longer.

• LEFT(Mona Mona_______________spaces_________lug 123;100)
• Mona Mona_______________spaces

And TRIM function will remove those extra spaces

• TRIM(Mona Mona_______________spaces)

leaving just

• Mona Mona

Hope it's clear.