Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Simple find with right function
We have a field of characters similar to:
product/small/GW1400A-GAVE.jpg We have unsuccessfully tried to use the RIGHT AND FIND functions to find all characters after the last "/" ( or from the right, all characters before up to the first /). In other words to extract the GW1400A-9AV.jpg Perhaps we are using the wrong functions. Any assistance would be appreciated. Richard |
#2
|
|||
|
|||
Simple find with right function
If you have a column of these thing, then use Text to Columns. Pull-down:
Data Text to Columns... and follow the wizard. Use the / as the separator. You will see: -- Gary''s Student "Richard Ruda" wrote: We have a field of characters similar to: product/small/GW1400A-GAVE.jpg We have unsuccessfully tried to use the RIGHT AND FIND functions to find all characters after the last "/" ( or from the right, all characters before up to the first /). In other words to extract the GW1400A-9AV.jpg Perhaps we are using the wrong functions. Any assistance would be appreciated. Richard |
#3
|
|||
|
|||
Simple find with right function
Richard,.
I assume there could 1, 2, 3 or any number of /. Assuming so, try this formula =MID(A1,FIND("~/~",SUBSTITUTE(A1,"/","~/~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","") )))+1,99) -- HTH RP (remove nothere from the email address if mailing direct) "Richard Ruda" wrote in message ... We have a field of characters similar to: product/small/GW1400A-GAVE.jpg We have unsuccessfully tried to use the RIGHT AND FIND functions to find all characters after the last "/" ( or from the right, all characters before up to the first /). In other words to extract the GW1400A-9AV.jpg Perhaps we are using the wrong functions. Any assistance would be appreciated. Richard |
#4
|
|||
|
|||
Simple find with right function
Hi!
Try this: =MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255) The tilde character "~" is used as a temporary marker. If your strings might already contain these characters then use some other character that is not likely to appear in your strings. Biff "Richard Ruda" wrote in message ... We have a field of characters similar to: product/small/GW1400A-GAVE.jpg We have unsuccessfully tried to use the RIGHT AND FIND functions to find all characters after the last "/" ( or from the right, all characters before up to the first /). In other words to extract the GW1400A-9AV.jpg Perhaps we are using the wrong functions. Any assistance would be appreciated. Richard |
#5
|
|||
|
|||
Simple find with right function
Try...
=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="/"),ROW(INDIRE CT("1:"&LEN(A1))))+1,1024) Hope this helps! In article , "Richard Ruda" wrote: We have a field of characters similar to: product/small/GW1400A-GAVE.jpg We have unsuccessfully tried to use the RIGHT AND FIND functions to find all characters after the last "/" ( or from the right, all characters before up to the first /). In other words to extract the GW1400A-9AV.jpg Perhaps we are using the wrong functions. Any assistance would be appreciated. Richard |
#6
|
|||
|
|||
Simple find with right function
Gary''s Student,
Perfect - thanks |
#7
|
|||
|
|||
Simple find with right function
Thanks Bob,
RR |
#8
|
|||
|
|||
Simple find with right function
Thanks Biff,
RR |
#9
|
|||
|
|||
Simple find with right function
Biff,
I notice that 3 different responders to this request suggest the MID function with some high number of characters to return, rather than the RIGHT function as in : =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) which seems to work well enough and doesn't need the catch-all value of 1,255 or whatever else. Am I missing something, or does RIGHT have some obscure problem that the experts are aware of and others are not? |
#10
|
|||
|
|||
Simple find with right function
No difference in this case except that mid is more flexible since it can be
used in all position while right or left do what they are named after so maybe Biff and other always use mid for that reason -- Regards, Peo Sjoblom "DOR" wrote in message oups.com... Biff, I notice that 3 different responders to this request suggest the MID function with some high number of characters to return, rather than the RIGHT function as in : =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) which seems to work well enough and doesn't need the catch-all value of 1,255 or whatever else. Am I missing something, or does RIGHT have some obscure problem that the experts are aware of and others are not? |
#11
|
|||
|
|||
Simple find with right function
Hi!
Am I missing something, or does RIGHT have some obscure problem that the experts are aware of and others are not? No, not really. Using RIGHT is perfectly acceptable but consider these differences (and this is what separates the "experts" from the rest of us!): =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) That formula contains 7 function calls and takes 86 keystrokes. =MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255) This formula has 6 function calls and takes 82 keystrokes. Biff "DOR" wrote in message oups.com... Biff, I notice that 3 different responders to this request suggest the MID function with some high number of characters to return, rather than the RIGHT function as in : =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) which seems to work well enough and doesn't need the catch-all value of 1,255 or whatever else. Am I missing something, or does RIGHT have some obscure problem that the experts are aware of and others are not? |
#12
|
|||
|
|||
Simple find with right function
Biff and Peo,
Thank you both - flexibility and efficiency trump intuitivity ... I guess Biff could have saved a few keystrokes and a few interpreter cycles by leaving out the comma in 1,255 or using a smaller number <g .... DOR |
#13
|
|||
|
|||
Simple find with right function
I guess Biff could have saved a few keystrokes and a few interpreter
cycles by leaving out the comma in 1,255 or using a smaller number <g The comma is the argument delimiter in the MID function! =MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255) FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1 That is the 2nd argument which tells MID at what position in the string to use as the extraction starting point. 255 is the 3rd argument which tells MID how many characters to return. 255 is used because you may never know exactly how many characters there are to the end of the string. Biff "DOR" wrote in message oups.com... Biff and Peo, Thank you both - flexibility and efficiency trump intuitivity ... I guess Biff could have saved a few keystrokes and a few interpreter cycles by leaving out the comma in 1,255 or using a smaller number <g ... DOR |
#14
|
|||
|
|||
Simple find with right function
DOR wrote...
Thank you both - flexibility and efficiency trump intuitivity ... .... Intuitivity, if it were an English word, I suppose would mean intuitiveness. Intuitiveness is subjective. Shorter formulas may not matter much when the overall formula length is fewer than 100 characters, but small formulas have a bad habbit of becoming small pieces of much longer formulas, and when formulas grow to several hundred characters and many repeated expressions, it's good to know how to shorten them. Also, Excel's substring matching functions only return position from the left of the string. If one wants all characters to the right of a given position (measured from the left), and if Excel's substring functions were picky, there'd be two ways to return it: RIGHT(string,LEN(string)-position) and MID(string,position+1,LEN(string)-position) Happily, these functions aren't picky, and effectively truncate their length arguments at the length of their string argument. Which means the MID call above is equivalent to MID(string,position+1,LEN(string)) and MID(string,position+1,<BIGNUMBER) where <BIGNUMBER is a placeholder for any moderately large positive whole number. And, FWIW, VBA's Mid function doesn't even require a 3rd argument to return the right substring beginning at the position given by its second argument. Using the worksheet MID function with a large 3rd argument is the simplest way to mimick VBA Mid semantics. That said, this is a situation in which using RIGHT may be more suitable. The shortest formula not using defined names may be the array formula =RIGHT(A1,MATCH("/",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0)-1) Using defined names, e.g., s referring to =ROW(INDIRECT("1:1024")), the shortest formula would be =MID(A1,LOOKUP(2,1/(MID("/"&A1,s,1)="/"),s),1024) which should return the whole string if there are no /s in it. If you want to get exotic, download and install Laurent Longre's MOREFUNC.XLL add-in and use =WMID(A11,-1,1,"/") and learn the joys of using negative integers to index from right to left. |
#15
|
|||
|
|||
Simple find with right function
But of course, Biff, but of course ...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can VLookup function find and list multiple records? | Excel Worksheet Functions | |||
Simple count function | Excel Worksheet Functions | |||
Function formula for simple worksheet | New Users to Excel | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
Add "FIND LINKS" to EXCEL Audit function | Excel Worksheet Functions |