One of those little things that sometimes needs to be done when working with many lines of information is the removal of an odd character here and there.
I was in just that position today. I had about 2,000 lines of information, and about 600 of them ended with a comma, and the rest didn’t.
I needed them all to be without the comma. Unfortunately, there were a number of other commas in the lines so I couldn’t just do a simple find and replace.
So, this is the excel code that I used:
=LEFT(A1, LEN(A1)-1)
This removed the last character from the data in cell A1.
I used the filter options to only select cells that ended with the comma, and then applied that code to those lines.
I then copied the entire column and pasted as text, where I needed the corrected information.
Other similar codes:
To remove the first few characters
=RIGHT(A1, LEN(A1)-3)
This will remove the first 3 characters of the cell content.
693.1 - 813,409
Another code in similar lines:
=MID(A1,FIND(“x”,A1)+1,LEN(A1)) will extract the text after the letter x
x can be any character in the string of characters in the cell, and this code will remove all characters from before the FIRST occurance of “x”
eg:
“sincexnice one” will result in “nice one”
“sincexnice x one” will result in “nice x one”
“since nice x one” will result in ” one” Note that the space is retained before the word “one”