Results 1 to 3 of 3

Thread: Extracting String between two types of characters

  1. #1
    Neophyte Sukhi's Avatar
    Join Date
    Nov 2018
    Location
    New Delhi
    Posts
    1
    Articles
    0
    Excel Version
    2010

    Extracting String between two types of characters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello! Everyone

    SBIPG NU6457493537 AMAZON MUMBAI--
    SBIPG NU6457493532 AMAZON Tokyo--
    SBIPG NU6457493535 AMAZON California--

    I am looking for a formula which helps me extract the string between the two underlined character. Please note that I want the string between the last number from the left to right which can vary from "1-9" while this character"--" in the rightmost end is same for all the data entries.

    Thanks in advance

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    778
    Articles
    0
    Excel Version
    2010
    In your example data, in each case the first character your wanting is position 19 in the start string. Does that apply to all of your data?
    Or:
    Is the first character always the second space in the start string?

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    778
    Articles
    0
    Excel Version
    2010
    As we haven't heard from you about the uniformity of your data, I thought I would post a solution anyway as others may find this useful. From the three sample main strings that you have provided, it does look
    like the SB and the NU references are a consistent number of characters (i.e.18), so building this into the formula will start the extraction from character 19. If the data should have records where character 19
    is not the second space then the formula may need to be changed. With those caveats try the following, assuming the main strings are from A2 down:

    =MID(A2,FIND("$",SUBSTITUTE(A2," ","$",2)),(FIND("--",A2,1)-1)-FIND("$",SUBSTITUTE(A2," ","$",2))+1) Copied Down

Tags for this Thread

Posting Permissions

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