+ Reply to Thread
Results 1 to 6 of 6

LEFT &RIGHT formula on the same cell

  1. #1
    Bonnie W.
    Guest

    LEFT &RIGHT formula on the same cell

    I was trying to take off hyphen within a text string, such as "0100-00-000".
    I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    on the same cell to return the last 3 digit but somehow the formula doesn't
    work. It won't give me a error message but just keep my formula in the cell.

    Please help and thanks

  2. #2
    Niek Otten
    Guest

    Re: LEFT &RIGHT formula on the same cell

    Format the cell as General and re-enter the formula.
    If you post again, do supply the formula.

    --
    Kind regards,

    Niek Otten

    "Bonnie W." <Bonnie W.@discussions.microsoft.com> wrote in message
    news:8B1B081C-41EB-4DB8-81A7-8D459FAE81B5@microsoft.com...
    >I was trying to take off hyphen within a text string, such as
    >"0100-00-000".
    > I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    > on the same cell to return the last 3 digit but somehow the formula
    > doesn't
    > work. It won't give me a error message but just keep my formula in the
    > cell.
    >
    > Please help and thanks




  3. #3
    Kevin B
    Guest

    RE: LEFT &RIGHT formula on the same cell

    Assuming that the string value is in A1, the following formula will strip the
    hyphens from the value:

    =LEFT(A1,FIND("-",A1,1)-1)&MID(A1,FIND("-",A1,1)+1,FIND("-",A1,FIND("-",A1,1)+1)-(FIND("-",A1,1)+1))&RIGHT(A1,3)
    --
    Kevin Backmann


    "Bonnie W." wrote:

    > I was trying to take off hyphen within a text string, such as "0100-00-000".
    > I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    > on the same cell to return the last 3 digit but somehow the formula doesn't
    > work. It won't give me a error message but just keep my formula in the cell.
    >
    > Please help and thanks


  4. #4
    Sloth
    Guest

    RE: LEFT &RIGHT formula on the same cell

    If you are not getting an error message, and you can see the formula after
    entered, then you probably have the cell formatted as text. Format it as
    general, and then re-edit the cell (select the cell, hit F2, hit enter).

    Also, you can try this formula. It will remove all "-" from the text string.

    =SUBSTITUTE(A1,"-","")

    "Bonnie W." wrote:

    > I was trying to take off hyphen within a text string, such as "0100-00-000".
    > I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    > on the same cell to return the last 3 digit but somehow the formula doesn't
    > work. It won't give me a error message but just keep my formula in the cell.
    >
    > Please help and thanks


  5. #5
    Danny@Kendal
    Guest

    Re: LEFT &RIGHT formula on the same cell

    "Bonnie W." <Bonnie W.@discussions.microsoft.com> wrote in message
    news:8B1B081C-41EB-4DB8-81A7-8D459FAE81B5@microsoft.com...
    >I was trying to take off hyphen within a text string, such as
    >"0100-00-000".
    > I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    > on the same cell to return the last 3 digit but somehow the formula
    > doesn't
    > work. It won't give me a error message but just keep my formula in the
    > cell.


    If the cell is formatted as 'text' then you'll need to change it to
    'general' and then edit the formula to refresh it.

    If you just want to remove the dashes from the string then try
    =SUBSTITUTE(A1,"-","")

    This returns the value as a string. If you want it to be interpreted as a
    number then add two minus signs like this
    =--SUBSTITUTE(A1,"-","")



  6. #6
    CLR
    Guest

    RE: LEFT &RIGHT formula on the same cell

    If all you want to do is eliminate the hyphens, you can do that with the Edit
    > Replace > Replace hyphen > with nothing (leave blank) > Replace all


    Vaya con Dios,
    Chuck, CABGx3


    "Bonnie W." wrote:

    > I was trying to take off hyphen within a text string, such as "0100-00-000".
    > I used LEFT formula to return the first 4 digit. Then I used RIGHT formula
    > on the same cell to return the last 3 digit but somehow the formula doesn't
    > work. It won't give me a error message but just keep my formula in the cell.
    >
    > Please help and thanks


+ 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