| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return a text that is before "/"
Dear All,
I have tried MID, FIND, LEFT function but to no avail as the length of a string can vary: 97/2008 98/2008 99/2008 108/2008 109/2008 495/2008 496/2008 CORRECTION 3/2008 498/2008 What I need to achieve is to have a function that will return everything before "/" and without any "space" at the end: e.g. it will return 498 only without emtpy space (something like TRIM). Any help appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return a text that is before "/"
a function that will return everything before "/" ..
For the above, this will return it as text/text nums In B1, copied down: =LEFT(A1,SEARCH("/",A1)-1) If "CORRECTION" is the only text that might be embedded within, and you need it returned as real numbers (ie nums before the "/"), try in B1, copied down: =LEFT(SUBSTITUTE(A1,"CORRECTION",""),SEARCH("/",SUBSTITUTE(A1,"CORRECTION",""))-1)+0 -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Piotr (Peter)" wrote: I have tried MID, FIND, LEFT function but to no avail as the length of a string can vary: 97/2008 98/2008 99/2008 108/2008 109/2008 495/2008 496/2008 CORRECTION 3/2008 498/2008 What I need to achieve is to have a function that will return everything before "/" and without any "space" at the end: e.g. it will return 498 only without emtpy space (something like TRIM). Any help appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return a text that is before "/"
Thanks a lot Max. That did it:))
"Max" wrote: a function that will return everything before "/" .. For the above, this will return it as text/text nums In B1, copied down: =LEFT(A1,SEARCH("/",A1)-1) If "CORRECTION" is the only text that might be embedded within, and you need it returned as real numbers (ie nums before the "/"), try in B1, copied down: =LEFT(SUBSTITUTE(A1,"CORRECTION",""),SEARCH("/",SUBSTITUTE(A1,"CORRECTION",""))-1)+0 -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Piotr (Peter)" wrote: I have tried MID, FIND, LEFT function but to no avail as the length of a string can vary: 97/2008 98/2008 99/2008 108/2008 109/2008 495/2008 496/2008 CORRECTION 3/2008 498/2008 What I need to achieve is to have a function that will return everything before "/" and without any "space" at the end: e.g. it will return 498 only without emtpy space (something like TRIM). Any help appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return a text that is before "/"
Welcome, glad it did.
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Piotr (Peter)" wrote in message ... Thanks a lot Max. That did it:)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can lookup return cell reference istead of "text" for sumif? | Excel Worksheet Functions | |||
| IF cells(row,column) contain "this word" ??? Then return text | New Users to Excel | |||
| Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
| Disabling "wrap text" neuters alt-return line feeds within cell | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
![Piotr (Peter)[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
