+ Reply to Thread
Results 1 to 17 of 17

Extract Mid Value from the string

  1. #1
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Extract Mid Value from the string

    Hi Friends,

    Please find my attachment, there is some data on sheet1 I want to extract the mid value from that data.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    Try:

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



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Extract Mid Value from the string

    Another.......

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    I think your solution is a little more succinct than mine. The good news is that they both produce the same output

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Extract Mid Value from the string

    @TMS
    You will agree that there are multiple approaches to achieve the same output. There are still so many options left.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Mid Value from the string

    I think it is the definition of the "middle" is where the challenge is

    =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",25)),25,50))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    The "worked examples" imply that it is between the first space and the " - ".

    I suspect that later examples may not comply with that. Your formula doesn't produce the results indicated in the workbook. You get "GOVIL -" rather than just "GOVIL"

    Regards, TMS

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Extract Mid Value from the string

    Here another one:
    =MID(A2,FIND(" ",A2;1)+1,FIND("-",A2,1)-FIND(" ",A2,1)-2)

    K P ASHOK KUMAR - COGP
    This is also a tricky one
    What result do you want?
    Last edited by popipipo; 12-19-2014 at 10:22 AM.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Extract Mid Value from the string

    Hi,


    Another one :

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


    Regards

    Edit:
    Oops, my formula is the same as popipipo's
    Last edited by karedog; 12-19-2014 at 10:24 AM.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    For comparison:


    A
    B
    C
    D
    E
    F
    1
    Name
    Sample Result
    TMS
    sktneer
    Allkey
    popipipo
    2
    A1 BOOK COMPANY - LFG1
    Book Company
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    3
    ARVIND GOVIL - COGP
    GOVIL
    GOVIL
    GOVIL
    GOVIL -
    GOVIL
    4
    BHIKAN TUKARAM JIWANE - FFDD
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    5
    BURJOR HOMI DANDIWALA - LFD2
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    6
    CHETAN VINAY NIMKAR - USGP
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    7
    CHIRAG JAMNALAL THAKKAR - LFGP
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    8
    COUNTRY FRESH PRODUCT - USWD
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    9
    D S INTEGRATED FINSEC PVT - FFGP
    S INTEGRATED FINSEC PVT
    S INTEGRATED FINSEC PVT
    S INTEGRATED
    S INTEGRATED FINSEC PVT
    10
    DINESH VASANTRAI BHUTA - LFD2
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    11
    GSD TRADING & FINANCIAL - FFGP
    TRADING & FINANCIAL
    TRADING & FINANCIAL
    TRADING &
    TRADING & FINANCIAL
    12
    HOOFRISH KEKI PATEL - LFDD
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    13
    IDBI BANK LTD - LFG1
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    14
    JAMSHID NAVAL PANDOLE - LFD2
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    15
    JAYANTKUMAR ASHAR - LFD2
    ASHAR
    ASHAR
    ASHAR -
    ASHAR
    16
    K P ASHOK KUMAR - COGP
    P ASHOK KUMAR
    P ASHOK KUMAR
    P ASHOK
    P ASHOK KUMAR
    17
    KEKI BURJORJI PATEL - LFDD
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    18
    LAXMI J MUNDKUR - COGP
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    19
    LCGC BIOANALYTIC SOLUTIONS - LFDD
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    20
    LILIA ROSS CARVALHO - USWD
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    21
    LUTHRA AND LUTHRA LAW - USD2
    AND LUTHRA LAW
    AND LUTHRA LAW
    AND LUTHRA
    AND LUTHRA LAW
    22
    MAQSOODA NOORUDIN TAWA - USGP
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Mid Value from the string

    Forgot about "-"

    =SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",25)),25,50)),"-","")

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string


    A
    B
    C
    D
    E
    F
    G
    1
    Name
    Sample Result
    TMS
    sktneer
    Allkey
    popipipo
    Allkey v2
    2
    A1 BOOK COMPANY - LFG1
    Book Company
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    3
    ARVIND GOVIL - COGP
    GOVIL
    GOVIL
    GOVIL
    GOVIL -
    GOVIL
    GOVIL
    4
    BHIKAN TUKARAM JIWANE - FFDD
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    5
    BURJOR HOMI DANDIWALA - LFD2
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    6
    CHETAN VINAY NIMKAR - USGP
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    7
    CHIRAG JAMNALAL THAKKAR - LFGP
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    8
    COUNTRY FRESH PRODUCT - USWD
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    9
    D S INTEGRATED FINSEC PVT - FFGP
    S INTEGRATED FINSEC PVT
    S INTEGRATED FINSEC PVT
    S INTEGRATED
    S INTEGRATED FINSEC PVT
    S INTEGRATED
    10
    DINESH VASANTRAI BHUTA - LFD2
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    11
    GSD TRADING & FINANCIAL - FFGP
    TRADING & FINANCIAL
    TRADING & FINANCIAL
    TRADING &
    TRADING & FINANCIAL
    TRADING &
    12
    HOOFRISH KEKI PATEL - LFDD
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    13
    IDBI BANK LTD - LFG1
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    14
    JAMSHID NAVAL PANDOLE - LFD2
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    15
    JAYANTKUMAR ASHAR - LFD2
    ASHAR
    ASHAR
    ASHAR -
    ASHAR
    ASHAR
    16
    K P ASHOK KUMAR - COGP
    P ASHOK KUMAR
    P ASHOK KUMAR
    P ASHOK
    P ASHOK KUMAR
    P ASHOK
    17
    KEKI BURJORJI PATEL - LFDD
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    18
    LAXMI J MUNDKUR - COGP
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    19
    LCGC BIOANALYTIC SOLUTIONS - LFDD
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    20
    LILIA ROSS CARVALHO - USWD
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    21
    LUTHRA AND LUTHRA LAW - USD2
    AND LUTHRA LAW
    AND LUTHRA LAW
    AND LUTHRA
    AND LUTHRA LAW
    AND LUTHRA
    22
    MAQSOODA NOORUDIN TAWA - USGP
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Mid Value from the string

    or this

    =TRIM(RIGHT(LEFT(A2,FIND(" -",A2)),LEN(LEFT(A2,FIND(" -",A2)))-FIND(" ",A2)))

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    Yay, we have a winner!

    All we need now is some input/feedback from the OP to tell us if he has enough solutions.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Name
    Sample Result
    TMS
    sktneer
    popipipo
    Allkey v3
    Allkey v2
    Allkey
    2
    A1 BOOK COMPANY - LFG1
    Book Company
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    BOOK COMPANY
    3
    ARVIND GOVIL - COGP
    GOVIL
    GOVIL
    GOVIL
    GOVIL
    GOVIL
    GOVIL
    GOVIL -
    4
    BHIKAN TUKARAM JIWANE - FFDD
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    TUKARAM JIWANE
    5
    BURJOR HOMI DANDIWALA - LFD2
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    HOMI DANDIWALA
    6
    CHETAN VINAY NIMKAR - USGP
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    VINAY NIMKAR
    7
    CHIRAG JAMNALAL THAKKAR - LFGP
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    JAMNALAL THAKKAR
    8
    COUNTRY FRESH PRODUCT - USWD
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    FRESH PRODUCT
    9
    D S INTEGRATED FINSEC PVT - FFGP
    S INTEGRATED FINSEC PVT
    S INTEGRATED FINSEC PVT
    S INTEGRATED FINSEC PVT
    S INTEGRATED FINSEC PVT
    S INTEGRATED
    S INTEGRATED
    10
    DINESH VASANTRAI BHUTA - LFD2
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    VASANTRAI BHUTA
    11
    GSD TRADING & FINANCIAL - FFGP
    TRADING & FINANCIAL
    TRADING & FINANCIAL
    TRADING & FINANCIAL
    TRADING & FINANCIAL
    TRADING &
    TRADING &
    12
    HOOFRISH KEKI PATEL - LFDD
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    KEKI PATEL
    13
    IDBI BANK LTD - LFG1
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    BANK LTD
    14
    JAMSHID NAVAL PANDOLE - LFD2
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    NAVAL PANDOLE
    15
    JAYANTKUMAR ASHAR - LFD2
    ASHAR
    ASHAR
    ASHAR
    ASHAR
    ASHAR
    ASHAR -
    16
    K P ASHOK KUMAR - COGP
    P ASHOK KUMAR
    P ASHOK KUMAR
    P ASHOK KUMAR
    P ASHOK KUMAR
    P ASHOK
    P ASHOK
    17
    KEKI BURJORJI PATEL - LFDD
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    BURJORJI PATEL
    18
    LAXMI J MUNDKUR - COGP
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    J MUNDKUR
    19
    LCGC BIOANALYTIC SOLUTIONS - LFDD
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    BIOANALYTIC SOLUTIONS
    20
    LILIA ROSS CARVALHO - USWD
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    ROSS CARVALHO
    21
    LUTHRA AND LUTHRA LAW - USD2
    AND LUTHRA LAW
    AND LUTHRA LAW
    AND LUTHRA LAW
    AND LUTHRA LAW
    AND LUTHRA
    AND LUTHRA
    22
    MAQSOODA NOORUDIN TAWA - USGP
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA
    NOORUDIN TAWA

  15. #15
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Extract Mid Value from the string

    Probably non of them works perfect.
    Nr 9, 16 and maybe 18 and 21 are a problem.
    Attached Files Attached Files

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Extract Mid Value from the string

    I think 18 is probably OK but the others may not be what is desired. As I said, useful to have input from the OP, fun though it is to find all the variations on a theme.

    Regards, TMS

  17. #17
    Registered User
    Join Date
    05-23-2014
    Posts
    43

    Re: Extract Mid Value from the string

    Thanks to my all friends.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract all the words from a string for processing, while leaving string intact.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2014, 02:02 PM
  2. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  3. [SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-04-2012, 01:49 PM
  4. Replies: 16
    Last Post: 10-11-2011, 12:31 PM
  5. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 AM

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