Hello,
We have a spreadsheet containing list of drawing nos, from which I want to extract the text part (i.e., fluid type) comes in between the drawing no to Column F. How can I accomplish this?
Attached sample spreadsheet.
Thank you in advance.
Hello,
We have a spreadsheet containing list of drawing nos, from which I want to extract the text part (i.e., fluid type) comes in between the drawing no to Column F. How can I accomplish this?
Attached sample spreadsheet.
Thank you in advance.
Try this equation in F3 copied down
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, 20)))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Okay, this seems to work
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&1234567890))<5, TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),44,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890,44))-44)), TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",20)),22, SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A3,"-",REPT(" ",20))&1234567890)-60)))
=substitute(if(min(search({0,1,2,3,4,5,6,7,8,9},a3&1234567890))<5, trim(mid(substitute(a3,"-",rept(" ",20)),44,min(search({0,1,2,3,4,5,6,7,8,9},substitute(a3,"-",rept(" ",20))&1234567890,44))-44)), trim(mid(substitute(a3,"-",rept(" ",20)),22, search({0,1,2,3,4,5,6,7,8,9},substitute(a3,"-",rept(" ",20))&1234567890)-60)))," ", "-")
Or this
=IF(OR(RIGHT(MID(A3,FIND("-",A3)+1,6))={"0","1","2","3","4","5","6","7","8","9","-"}),IF(ISERROR(MID(A3,4,1)+0),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25,25)),TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",25)),25*2,25))),MID(A3,FIND("-",A3)+1,6))
Last edited by AlKey; 11-25-2014 at 06:55 PM.
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
Hello… when I copy some more drawing nos. in Column A (SAMPLE file attached and highlighted in green), and copy the fomula down, it gives some error result.. Please let me know how to fix it..
There reason you get errors is because your data has changed from the original file and some new conditions are now present. At this point I would like to offer you a different approach to this problem which may work even better for you. So, instead of extracting codes from the data set, simply use code look up from the list a created in column H and this formula in F3.
=LOOKUP(25^25,SEARCH($H$3:$H$17,A3),$H$3:$H$17)
The code list can be extended
FWIW this works at my end (column I) with all the Drawing No. you have listed in your latest upload....though admittedly not as elegant and simple as the others.
Entered in I3 and copied down:
Formula:Please Login or Register to view this content.
File attached.
BTW: I have not, yet looked at AlKey's latest upload.
Last edited by FlameRetired; 11-28-2014 at 07:23 PM.
You're welcome. Glad it works, and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks