Deleting a "character"

[s7y]

Hello again everybody,



in cells A1:A45 I want to paste a text that comes from an email. I've formatted the column as text. This is how the text that I want to paste looks like:

-ML/N

but when I paste it this is what I get

'-ML/N

I am using the following formula to extract a specific text from a line (thank you again Catalin):

=IFERROR(LEFT(SUBSTITUTE(A1;LEFT(A1;FIND("/";A1));"");FIND("/";SUBSTITUTE(A1;LEFT(A1;FIND("/";A1));""))-1);"")

At this point the cells that should be blank contain the apostophe.

Is there a way to eliminate this?

Thank you all

[Catalin.B]

You mean you actually see in the cell that apostrophe?
The normal behaviour of excel, when user starts typing the sign "-" minus, is to treat what folows as a formula, and automatically places the "=" equal sign before user entries. (after exiting the cell) To avoid this default behaviour, and to "tell" excel that you want to type a text string in that cell, just start typing with an apostrophe folowed by your text.
The nice part for this method of typing text, is that you cannot really see in the cell the apostrophe, ONLY in the formula bar! It can be seen in the cell only in cell edit mode.
If you can see that in the cell and that cell is not in edit mode, well, that is a problem.
You can try to remove it with SUBSTITUTE function, which completes your formula:
=SUBSTITUTE(IFERROR(LEFT(SUBSTITUTE(A1;LEFT(A1;FIN D("/";A1));"");FIND("/";SUBSTITUTE(A1;LEFT(A1;FIND("/";A1));""))-1);"");"'";"")

[s7y]

thank you very much again