Get part of text in a Cell

[Stan Brown]

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

The function you want is called MID( ).

 

[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

Thank you for easing a lot of minds

 

[Pete_UK]

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

 

[FSt1]

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]

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[Pete_UK]

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

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------


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

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[Pete_UK]

Well, you'll also need to look for the closing square bracket because you
might just have one without the other.

Pete

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 !
-----------------------------------------------------------------------------------------------


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

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

Well, you'll also need to look for the closing square bracket because you
might just have one without the other.

Pete

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 !
-----------------------------------------------------------------------------------------------


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

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[Marcha]

Thank you so much Pete - Exactly what I wanted

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

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?

Put this formula in J6:

=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)

Hope this helps.

Pete

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

Thank you for easing a lot of minds

 

[Pete_UK]

You're welcome, Marcha - thanks for feeding back.

Pete

Thank you so much Pete - Exactly what I wanted

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

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 - ifthe
Member is new... There comes a #Value in Cell - and want nothing - just
blank. Is that possible?
:
Put this formula in J6:
=MID(F6,FIND("[",F6),FIND("]",F6)-FIND("[",F6)+1)
Hope this helps.
Pete
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
Thank you for easing a lot of minds - Hide quoted text -

- Show quoted text -