Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Remove a certain charcter from excel (https://www.askmehelpdesk.com/showthread.php?t=560425)

  • Mar 7, 2011, 06:21 PM
    tpipkin
    Remove a certain charcter from excel
    I need to delete the first (-) for example 101-1-7 need to change to 1011-7
  • Mar 7, 2011, 07:40 PM
    ScottGem

    Is the pattern ALWAYS xxx-x-x
  • Mar 7, 2011, 07:43 PM
    tpipkin
    Yes the pattern is always the same
  • Mar 7, 2011, 07:51 PM
    ScottGem

    =Left(A1,3) & Right(A1,3)
  • Mar 7, 2011, 07:52 PM
    tpipkin
    Yes the pattern is always the same
  • Mar 7, 2011, 08:01 PM
    tpipkin
    Scott Do I copy and paste this somewhere inside of excel?
  • Mar 8, 2011, 04:41 AM
    ScottGem

    Ok, I'll assume you have a column of cells with the data you want to change. Otherwise you would just click in the cell and edit that one cell.

    So what you do is enter the formula in a cell in an empty column, usually to the right of the column you want to edit. You change the cell reference (A1) in what I gave you to the correct cell for your sheet. Next you copy that formula down the column as far as you need to. The final step is to highlight the column with the formula and select Copy, then Paste Special and paste the values over the original column. Do this in a copy in case something goes wrong.
  • Mar 8, 2011, 06:22 AM
    tpipkin
    Scott;
    Sorry to sound slow, but I am still not getting it I have attached a sample of the file I need to change if you can send me a screen shot maybe that will help,
  • Mar 8, 2011, 09:12 AM
    ScottGem
    1 Attachment(s)

    Nothing attached.

    But I've attached a sample for you. In Column A are examples of your pattern. In Column B are the formulas to remove the first -. In Column C I used Paste Special to paste the results of the formula in Col B.
  • Mar 8, 2011, 09:25 AM
    tpipkin
    Scott do I place the formula in column B at the top as in your example? Or do I select column A and it will put the correct answer in B? Sorry for taking some much of your time
  • Mar 8, 2011, 01:09 PM
    ScottGem

    It depends on YOUR sheet. You generally put the formula in the cell to the right of where your text is, then copy it down the column.
  • Mar 8, 2011, 02:22 PM
    tpipkin
    I hope my attachment loads this time, I have copied and paste some actual data from my file, and some of them seem to be different I hope you can help the first formula works as long as they are not double digit.
    102-4-25
    102-4-26
    102-4-27
    102-4-28
    102-4-29
    102-4-30
    102-4-32
    102-4-33


    Thanks Scott


  • Mar 8, 2011, 02:30 PM
    ScottGem

    See that's why I asked if the pattern was always the same.

    Change the formula to:
    Left(A1,3) & Right(A1,Len(A1)-4)
  • Mar 8, 2011, 02:36 PM
    tpipkin
    The first formula worked with the pipe, and not the comma should that be changed?
    =LEFT(A3|3) & RIGHT(A3|3)
    =Left(A1,3) & Right(A1,Len(A1)-4)
  • Mar 8, 2011, 02:45 PM
    ScottGem

    Hmm, worked for me with the comma. But if it works for you with the pipe instead then use that.

  • All times are GMT -7. The time now is 01:20 PM.