Remove a specific character at the end of a cell

[mcconnelln89]

Hello all I'm having an issue with removing a specific character at the end of each cell where It occurs.

[TABLE="width: 186"]
<tbody>[TR]
[TD="colspan: 3"];2-2/5X1-3/10|;2-2/5X1-3/10[/TD]
[/TR]
[TR]
[TD="colspan: 3"];1-3/4X1-7/10|;1-3/4X1-7/10|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];1-3/4X1-7/10|;1-3/4X1-7/10[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-7/25X1-1/4|;2-7/25X1-1/4[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-1/2X1-1/4|;2-1/2X1-1/4|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-7/10X1-1/4|;2-7/10X1-1/4|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-22/25X1-1/4|;2-22/25X1-1/4|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-7/25X1-1/4|;2-7/25X1-1/4|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-2/5X1-9/20|;2-2/5X1-9/20|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-2/5X1-9/20|;2-2/5X1-9/20[/TD]
[/TR]
[TR]
[TD="colspan: 3"];7/8X1-1/4|;7/8X1-1/4|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];3X2-17/100|;3X2-17/100|[/TD]
[/TR]
[TR]
[TD="colspan: 2"];2-2/5X1|;2-2/5X1|[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"];2-1/10X1-1/4|;2-1/10X1-1/4[/TD]
[/TR]
[TR]
[TD="colspan: 3"];7/8X1-1/4|;7/8X1-1/4[/TD]
[/TR]
[TR]
[TD="colspan: 3"];17/20X1-7/10|;17/20X1-7/10|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];1-4/5X1-1/10|;1-4/5X1-1/10|[/TD]
[/TR]
[TR]
[TD="colspan: 3"];1-4/5X1-1/10|;1-4/5X1-1/10|[/TD]
[/TR]
</tbody><colgroup><col span="3"></colgroup>[/TABLE]


This is just an example. I need to remove the | an only the | at the end of the cells where it occurs. I cannot use Kutools to remove that specific character because I need it as a deliminator for a script I have to run later and it occurs in the middle of the cells and I need it there. I also cannot remove just the last character from every cell because not every cell ends with that character. Is there a way I can remove just the | at the end of the cells where it occurs?
Thanks!

 

[easy2understandexcel]

Hi.
Assuming I understand your question correctly:
=IF(RIGHT(A1,1)="|",LEFT(A1,LEN(A1)-1),A1)

Hope this helps!

Please check out my excel youtube channel Excel 101

 

[mcconnelln89]

Thank you for your quick response. How do I apply this? I tried pasting it to a blank cell and modifying the cell values and it puts the finished product in the blank cell I paste it in but not in the target cell. Also it does not work correctly when I try to drag it down and apply it to all of the following cells.

 

[hiker95]

mcconnelln89,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that does not do any looping in the cells in column A.

Sample raw data:

Excel 2007
	A
1	;2-2/5X1-3/10|;2-2/5X1-3/10
2	;1-3/4X1-7/10|;1-3/4X1-7/10|
3	;1-3/4X1-7/10|;1-3/4X1-7/10
4	;2-7/25X1-1/4|;2-7/25X1-1/4
5	;2-1/2X1-1/4|;2-1/2X1-1/4|
6	;2-7/10X1-1/4|;2-7/10X1-1/4|
7	;2-22/25X1-1/4|;2-22/25X1-1/4|
8	;2-7/25X1-1/4|;2-7/25X1-1/4|
9	;2-2/5X1-9/20|;2-2/5X1-9/20|
10	;2-2/5X1-9/20|;2-2/5X1-9/20
11	;7/8X1-1/4|;7/8X1-1/4|
12	;3X2-17/100|;3X2-17/100|
13	;2-2/5X1|;2-2/5X1|
14	;2-1/10X1-1/4|;2-1/10X1-1/4
15	;7/8X1-1/4|;7/8X1-1/4
16	;17/20X1-7/10|;17/20X1-7/10|
17	;1-4/5X1-1/10|;1-4/5X1-1/10|
18	;1-4/5X1-1/10|;1-4/5X1-1/10|
19
Sheet1

After the macro:

Excel 2007
	A
1	;2-2/5X1-3/10|;2-2/5X1-3/10
2	;1-3/4X1-7/10|;1-3/4X1-7/10
3	;1-3/4X1-7/10|;1-3/4X1-7/10
4	;2-7/25X1-1/4|;2-7/25X1-1/4
5	;2-1/2X1-1/4|;2-1/2X1-1/4
6	;2-7/10X1-1/4|;2-7/10X1-1/4
7	;2-22/25X1-1/4|;2-22/25X1-1/4
8	;2-7/25X1-1/4|;2-7/25X1-1/4
9	;2-2/5X1-9/20|;2-2/5X1-9/20
10	;2-2/5X1-9/20|;2-2/5X1-9/20
11	;7/8X1-1/4|;7/8X1-1/4
12	;3X2-17/100|;3X2-17/100
13	;2-2/5X1|;2-2/5X1
14	;2-1/10X1-1/4|;2-1/10X1-1/4
15	;7/8X1-1/4|;7/8X1-1/4
16	;17/20X1-7/10|;17/20X1-7/10
17	;1-4/5X1-1/10|;1-4/5X1-1/10
18	;1-4/5X1-1/10|;1-4/5X1-1/10
19
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveSpecialCharacterFromEndOfCell()
' hiker95, 08/25/2015, ME878282
Dim Addr As String
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(Right(@,1)=""|"",LEFT(@,LEN(@)-1),@)", "@", Addr))
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveSpecialCharacterFromEndOfCell macro.