+ Reply to Thread
Results 1 to 6 of 6

Returning left part of cell before a character

  1. #1
    Alan
    Guest

    Returning left part of cell before a character

    I have a text file report that is not customizable. I import that into
    Excel. There is a field where relevant data ends after the colon
    character ":". Can someone help me with a formula I can put in column C
    that evaluates the corresponding cell in column B and brings back all
    text to the left of the colon mark?


    Examples:
    Column B

    freddy: 45345 sldf
    jeff: 45422 fdflh
    sam: bfd4454

    In this case I want to return the characters to the left of the colon:
    freddy
    jeff
    sam

    Thanks in Advance!
    Alan

  2. #2
    Naz
    Guest

    RE: Returning left part of cell before a character

    Hi there,

    You can use the formula

    =MID(B1,1,FIND(":",B1)-1)

    This will bring back all the text before the colon.

    Hope that helps.

    Naz,
    London

    "Alan" wrote:

    > I have a text file report that is not customizable. I import that into
    > Excel. There is a field where relevant data ends after the colon
    > character ":". Can someone help me with a formula I can put in column C
    > that evaluates the corresponding cell in column B and brings back all
    > text to the left of the colon mark?
    >
    >
    > Examples:
    > Column B
    >
    > freddy: 45345 sldf
    > jeff: 45422 fdflh
    > sam: bfd4454
    >
    > In this case I want to return the characters to the left of the colon:
    > freddy
    > jeff
    > sam
    >
    > Thanks in Advance!
    > Alan
    >


  3. #3
    Gord Dibben
    Guest

    Re: Returning left part of cell before a character

    Alan

    Data>Text to Columns>Delimited by other. Enter a colon. Next.

    Select the right-hand column and "do not import column". Finish.


    Gord Dibben Excel MVP

    On Sat, 22 Jan 2005 14:35:49 -0600, Alan <[email protected]> wrote:

    >I have a text file report that is not customizable. I import that into
    >Excel. There is a field where relevant data ends after the colon
    >character ":". Can someone help me with a formula I can put in column C
    >that evaluates the corresponding cell in column B and brings back all
    >text to the left of the colon mark?
    >
    >
    >Examples:
    >Column B
    >
    >freddy: 45345 sldf
    >jeff: 45422 fdflh
    >sam: bfd4454
    >
    >In this case I want to return the characters to the left of the colon:
    >freddy
    >jeff
    >sam
    >
    >Thanks in Advance!
    >Alan



  4. #4
    Myrna Larson
    Guest

    Re: Returning left part of cell before a character

    For a formula

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

    Could also use SEARCH instead of FIND. See Help for the differences (which
    aren't relevant here).

    >On Sat, 22 Jan 2005 14:35:49 -0600, Alan <[email protected]> wrote:
    >
    >>I have a text file report that is not customizable. I import that into
    >>Excel. There is a field where relevant data ends after the colon
    >>character ":". Can someone help me with a formula I can put in column C
    >>that evaluates the corresponding cell in column B and brings back all
    >>text to the left of the colon mark?
    >>
    >>
    >>Examples:
    >>Column B
    >>
    >>freddy: 45345 sldf
    >>jeff: 45422 fdflh
    >>sam: bfd4454
    >>
    >>In this case I want to return the characters to the left of the colon:
    >>freddy
    >>jeff
    >>sam
    >>
    >>Thanks in Advance!
    >>Alan



  5. #5
    Ron Rosenfeld
    Guest

    Re: Returning left part of cell before a character

    On Sat, 22 Jan 2005 14:35:49 -0600, Alan <[email protected]> wrote:

    >I have a text file report that is not customizable. I import that into
    >Excel. There is a field where relevant data ends after the colon
    >character ":". Can someone help me with a formula I can put in column C
    >that evaluates the corresponding cell in column B and brings back all
    >text to the left of the colon mark?
    >
    >
    >Examples:
    >Column B
    >
    >freddy: 45345 sldf
    >jeff: 45422 fdflh
    >sam: bfd4454
    >
    >In this case I want to return the characters to the left of the colon:
    >freddy
    >jeff
    >sam
    >
    >Thanks in Advance!
    >Alan


    Assuming the fields are in columns, you can use the Data/Text to Columns wizard
    and use the colon as the delimiter. (Select Other and type in a colon : in the
    box next to it).


    --ron

  6. #6
    Aladin Akyurek
    Guest

    Re: Returning left part of cell before a character

    In case you have to apply a formula...

    =TRIM(LEFT(A2,SEARCH(":",A2&" :")-1))

    which would handle entries with no colon and empty cells in A.

    Alan wrote:
    > I have a text file report that is not customizable. I import that into
    > Excel. There is a field where relevant data ends after the colon
    > character ":". Can someone help me with a formula I can put in column C
    > that evaluates the corresponding cell in column B and brings back all
    > text to the left of the colon mark?
    >
    >
    > Examples:
    > Column B
    >
    > freddy: 45345 sldf
    > jeff: 45422 fdflh
    > sam: bfd4454
    >
    > In this case I want to return the characters to the left of the colon:
    > freddy
    > jeff
    > sam
    >
    > Thanks in Advance!
    > Alan


+ 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