Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
I don't know how to search for this Function - but I'm sure I have seen it.
I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
hi
yes you have seen it. look up the =left(), =right() and =mid() functions in xl help. i think that is what your are looking for. Regards FSt1 "Marcha" wrote: I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
Put this formula in J6:
=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
Thank you Pete - very useful (copy/paste)... I just had to change the commas
(,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
You can do it like this:
=IF(ISERROR(MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)),"",MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)) Hope this helps. Pete "Marcha" wrote in message ... Thank you Pete - very useful (copy/paste)... I just had to change the commas (,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
How about:
=IF(ISNUMBER(FIND("[",F6)),MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1),"") ? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Pete_UK" wrote in message ... You can do it like this: =IF(ISERROR(MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)),"",MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)) Hope this helps. Pete "Marcha" wrote in message ... Thank you Pete - very useful (copy/paste)... I just had to change the commas (,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
Well, you'll also need to look for the closing square bracket because you
might just have one without the other. Pete "RagDyeR" wrote in message ... How about: =IF(ISNUMBER(FIND("[",F6)),MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1),"") ? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Pete_UK" wrote in message ... You can do it like this: =IF(ISERROR(MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)),"",MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)) Hope this helps. Pete "Marcha" wrote in message ... Thank you Pete - very useful (copy/paste)... I just had to change the commas (,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
Thank you so much Pete - Exactly what I wanted :)
"Pete_UK" wrote: You can do it like this: =IF(ISERROR(MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)),"",MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)) Hope this helps. Pete "Marcha" wrote in message ... Thank you Pete - very useful (copy/paste)... I just had to change the commas (,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
You're welcome, Marcha - thanks for feeding back.
Pete On Mar 11, 3:40*pm, Marcha wrote: Thank you so much Pete - Exactly what I wanted :) "Pete_UK" wrote: You can do it like this: =IF(ISERROR(MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)),"",MID(F6,FIN*D("[",F6),FIND("]",F6)-FIND("[",F6)+1)) Hope this helps. Pete "Marcha" wrote in message ... Thank you Pete - very useful (copy/paste)... I just had to change the commas (,) with simmi collons (;) Now I would like to get no results if the 'Code' is not there yet - if the Member is new... There comes a #Value in Cell - and want nothing - just blank. Is that possible? "Pete_UK" wrote: Put this formula in J6: =MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1) Hope this helps. Pete "Marcha" wrote in message ... I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D Thank you for easing a lot of minds :)- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Get part of text in a Cell
Tue, 11 Mar 2008 03:58:02 -0700 from Marcha
: I don't know how to search for this Function - but I'm sure I have seen it. I have a text-cell - and in that cell I want to retrieve a 'Code' that is placed within the cell. The 'Code' is between a [ and ] - like: [OT] 1 june. I want the [OT] to be written in another cell in the row. (Don't wanna remove it from the current cell - just copy it) I know I can get the posistion with FIND("[";F6) and FIND("]";F6) Now I just want to place it in J6 - something like: GetText(F6;1;3) - and the result would be [OT] in the cell F6 :D The function you want is called MID( ). -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
USE PART OF TEXT FROM ONE CELL IN ANOTHER | Excel Discussion (Misc queries) | |||
delete part of text from a cell | Excel Worksheet Functions | |||
Display contents of cell in another cell as part of text string? | New Users to Excel | |||
format part of text in a cell? | Excel Discussion (Misc queries) |