| Home |
| Search |
| Today's Posts |
|
#1
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... |
|
#2
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 :) |
|
#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
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 :) |
|
#5
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 :) |
|
#6
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 :) |
|
#7
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 :) |
|
#8
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 :) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Get part of text in a Cell
I kind of doubt that ... but it's up to the OP to mention that.<bg
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Pete_UK" wrote in message ... 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 :) |
|
#10
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 :) |
|
#11
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 - |
| 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) | |||
Linear Mode
