+ Reply to Thread
Results 1 to 8 of 8

How to extract names from a text string?

  1. #1
    Registered User
    Join Date
    11-24-2009
    Location
    OSLO
    MS-Off Ver
    Excel 2003
    Posts
    66

    How to extract names from a text string?

    Can you help me with this?
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: 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?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: How to extract names from a text 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
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: How to extract names from a text string?

    Here, try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-24-2009
    Location
    OSLO
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: How to extract names from a text string?

    thanks, this is great..

    Could you make the formula, so it always returns 2 words after "fra"? If you just have this formula:

    =deltekst(A4;søk("fra";A4)+4;255)

    I guess instead og 255 in the last expression, I have to write something else.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: How to extract names from a text string?

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-24-2009
    Location
    OSLO
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: How to extract names from a text string?

    Ups, I see that it works, but could you explain the steps zbor?

    =TRIM(LEFT(SUBSTITUTE(MID(A3;SEARCH("Fra ";A3&"Fra ")+4;255);" ";REPT(" ";255);2);100))

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: How to extract names from a text string?

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1