View Full Version : Remove a certain charcter from excel
tpipkin
Mar 7, 2011, 06:21 PM
I need to delete the first (-) for example 101-1-7 need to change to 1011-7
ScottGem
Mar 7, 2011, 07:40 PM
Is the pattern ALWAYS xxx-x-x
tpipkin
Mar 7, 2011, 07:43 PM
Yes the pattern is always the same
ScottGem
Mar 7, 2011, 07:51 PM
=Left(A1,3) & Right(A1,3)
tpipkin
Mar 7, 2011, 07:52 PM
Yes the pattern is always the same
tpipkin
Mar 7, 2011, 08:01 PM
Scott Do I copy and paste this somewhere inside of excel?
ScottGem
Mar 8, 2011, 04:41 AM
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.
tpipkin
Mar 8, 2011, 06:22 AM
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,
ScottGem
Mar 8, 2011, 09:12 AM
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.
tpipkin
Mar 8, 2011, 09:25 AM
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
ScottGem
Mar 8, 2011, 01:09 PM
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.
tpipkin
Mar 8, 2011, 02:22 PM
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
ScottGem
Mar 8, 2011, 02:30 PM
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)
tpipkin
Mar 8, 2011, 02:36 PM
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)
ScottGem
Mar 8, 2011, 02:45 PM
Hmm, worked for me with the comma. But if it works for you with the pipe instead then use that.