+ Reply to Thread
Results 1 to 5 of 5

EXTRACT TEXT FROM TEXT STRING:The names are of variable length

  1. #1
    carricka
    Guest

    EXTRACT TEXT FROM TEXT STRING:The names are of variable length

    Hi Folks -

    Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from
    Word 2003, to extract a name from cells that contain a name and a number.
    The names are of variable length as are the numbers. In all cases there is a
    space between the name and the number.
    Examples are:
    Anthony 2
    Ber 12
    Philomena 123.

    I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from
    the space or right to the space.

    Many thanks.
    ANTHONY


  2. #2
    Barb R.
    Guest

    re: EXTRACT TEXT FROM TEXT STRING:The names are of variable length

    Try this:

    =MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))

    You could also parse the data using DATA -> TEXT TO COLUMNS

    "carricka" wrote:

    > Hi Folks -
    >
    > Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from
    > Word 2003, to extract a name from cells that contain a name and a number.
    > The names are of variable length as are the numbers. In all cases there is a
    > space between the name and the number.
    > Examples are:
    > Anthony 2
    > Ber 12
    > Philomena 123.
    >
    > I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from
    > the space or right to the space.
    >
    > Many thanks.
    > ANTHONY
    >


  3. #3
    Geoff Lord
    Guest

    re: EXTRACT TEXT FROM TEXT STRING:The names are of variable length

    Try

    =MID(A5,1,SEARCH(" ",A5))

    Where text is in A5

    Geoff


    "carricka" <carricka@discussions.microsoft.com> wrote in message
    news:F327B045-9E4A-4722-A37C-04E27B452033@microsoft.com...
    > Hi Folks -
    >
    > Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from
    > Word 2003, to extract a name from cells that contain a name and a number.
    > The names are of variable length as are the numbers. In all cases there

    is a
    > space between the name and the number.
    > Examples are:
    > Anthony 2
    > Ber 12
    > Philomena 123.
    >
    > I've looked at LEFT and RIGHT and TRIM but can't see any way to go left

    from
    > the space or right to the space.
    >
    > Many thanks.
    > ANTHONY
    >




  4. #4
    carricka
    Guest

    re: EXTRACT TEXT FROM TEXT STRING:The names are of variable length

    Hi Barb,

    This gives me the number instead of the name. It's not what I want but is
    most useful anyway. Geoff has supplied a formula that gives me the name.
    Thanks very much for your time.

    ANTHONY

    "Barb R." wrote:

    > Try this:
    >
    > =MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))
    >
    > You could also parse the data using DATA -> TEXT TO COLUMNS
    >
    > "carricka" wrote:
    >
    > > Hi Folks -
    > >
    > > Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from
    > > Word 2003, to extract a name from cells that contain a name and a number.
    > > The names are of variable length as are the numbers. In all cases there is a
    > > space between the name and the number.
    > > Examples are:
    > > Anthony 2
    > > Ber 12
    > > Philomena 123.
    > >
    > > I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from
    > > the space or right to the space.
    > >
    > > Many thanks.
    > > ANTHONY
    > >


  5. #5
    carricka
    Guest

    re: EXTRACT TEXT FROM TEXT STRING:The names are of variable length

    Hi Geoff -

    Works perfectly, thanks.

    ANTHONY

    "Geoff Lord" wrote:

    > Try
    >
    > =MID(A5,1,SEARCH(" ",A5))
    >
    > Where text is in A5
    >
    > Geoff
    >
    >
    > "carricka" <carricka@discussions.microsoft.com> wrote in message
    > news:F327B045-9E4A-4722-A37C-04E27B452033@microsoft.com...
    > > Hi Folks -
    > >
    > > Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from
    > > Word 2003, to extract a name from cells that contain a name and a number.
    > > The names are of variable length as are the numbers. In all cases there

    > is a
    > > space between the name and the number.
    > > Examples are:
    > > Anthony 2
    > > Ber 12
    > > Philomena 123.
    > >
    > > I've looked at LEFT and RIGHT and TRIM but can't see any way to go left

    > from
    > > the space or right to the space.
    > >
    > > Many thanks.
    > > ANTHONY
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1