+ Reply to Thread
Results 1 to 4 of 4

Split a Cell Reference in Half

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    63

    Split a Cell Reference in Half

    Hi all,

    I have two sheets. One containing in cell B2 "1211 - Extra Core Hours" (t_Cost_Centre_data) and then another sheet that displays the data. On the display sheet, there need to be two fields. Event ID and Event Name. The event ID is the "1211" and the name "Extra Core Hours".

    My question is, how do I split the reference so that only the ID and name appear in the relevant cell?

    Ie Event ID Event Name
    1211 Extra Core Hours

    Note, the ID's and names will change and they will be multiple.

    Thanks!

    Example file linked below
    Formula Help!.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Split a Cell Reference in Half

    Hi

    This formula is for extracting ID:
    Please Login or Register  to view this content.
    This formula is for extracting name:
    Please Login or Register  to view this content.
    Click *, if my suggestion helps you. Have a good day!!

  3. #3
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Split a Cell Reference in Half

    To get the ID:

    =LEFT(B2,FIND(" -",B2)-1)

    To get the name:

    =MID(B2,FIND("- ",B2)+2,255)

  4. #4
    Registered User
    Join Date
    11-07-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Split a Cell Reference in Half

    Quote Originally Posted by jraj1106 View Post
    Hi

    This formula is for extracting ID:
    Please Login or Register  to view this content.
    This formula is for extracting name:
    Please Login or Register  to view this content.
    Works a treat, thank you!

    You to Andrew

+ 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