Hi Friends,
Please find my attachment, there is some data on sheet1 I want to extract the mid value from that data.
Hi Friends,
Please find my attachment, there is some data on sheet1 I want to extract the mid value from that data.
Try:
Formula: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
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.
I think your solution is a little more succinct than mine. The good news is that they both produce the same output
@TMS
You will agree that there are multiple approaches to achieve the same output. There are still so many options left.
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
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
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
Hi,
Another one :
Formula: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
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
Forgot about "-"
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",25)),25,50)),"-","")
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
or this
=TRIM(RIGHT(LEFT(A2,FIND(" -",A2)),LEN(LEFT(A2,FIND(" -",A2)))-FIND(" ",A2)))
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
Probably non of them works perfect.
Nr 9, 16 and maybe 18 and 21 are a problem.
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
Thanks to my all friends.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks