I was working on a project recently when I came across this combined field which had a weird character at the end of cell. This wasn’t the same character, it kept changing and the cell lengths were all different. So I couldn’t do Text to Columns. and I couldn’t do Edit Replace to get rid of it. So how would I do this?
Excel file to play along: http://excelbyjoe.com/wp-content/uploads/2015/04/remove-last-character.xlsx
Here is the spreadsheet:
So I have these months but there is a weird character at the end.
How would you get rid of it?
The first way I thought of was to get the length of the cell and then use the LEFT function to pull the whole thing, less 1 cell.
What this formula is doing is taking the LEFT most cells from cell A1 which is Januaryf.
To get the number of cells it is pulling, it takes the length of the cell (LEN), which is 8 and subtracts 1 from it. So ultimately, the formula is:
So that’s how it pulls January. Then copy this down and get the month corrected for all the cells.
The second method I thought of was through the REPLACE function. Basically replace the last character with nothing.
What we are doing here is using the REPLACE function.
We are Replacing what is in cell A1.
The second part asks what character to start the replace. We used LEN(A1), so it starts at the last character.
The third part is a 1. That is how many characters do we want to replace. We only want to replace the last one.
The final part is “”. That is saying to replace the last character with “”, which basically means to replace it with nothing.
So that is it. Two methods to replace the final character in a cell. This will work for replacing the final two characters or three, or replacing the first character. You just need to adjust the formulas.
Do you have any other methods to do this? Post it in the comments.