replace function changes the format of the cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1716420%22%20slang%3D%22en-US%22%3Ereplace%20function%20changes%20the%20format%20of%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1716420%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20replace%20function%20is%20changing%20the%20format%20of%20cells%20in%20certain%20situations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20that%20is%20formatted%20as%20text%20and%20contains%20the%20value%20%2201-2000%26nbsp%3B%20%26nbsp%3B%22%26nbsp%3B%20(3%20spaces%20at%20the%20end)%26nbsp%3B%20It%20is%20a%20code%20and%20not%20a%20date.%26nbsp%3B%20I%20would%20like%20to%20remove%20the%20spaces%20at%20the%20end%20so%20I%20used%20the%20find%20and%20replace%20function%20and%20in%20the%20Find%20What%3A%20field%2C%20I%20put%20a%20space%20and%20leave%20the%20Replace%20With%3A%20field%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20click%20on%20replace%20all%2C%20the%203%20spaces%20are%20gone%20but%20my%20cell%20now%20is%20now%20showing%20%22Jan-00%22%20and%20formatted%20as%20a%20custom%20field%20with%20mmm-yy.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20able%20to%20use%20the%20trim()%20function%20and%20remove%20the%20spaces%20and%20copy%20and%20paste%20the%20value%20in%20a%20new%20cell%20but%20why%20is%20the%20replace%20function%20changing%20the%20format%20of%20a%20cell%3F%20Is%20there%20a%20way%20to%20prevent%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20M%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1716420%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1716489%22%20slang%3D%22de-DE%22%3ESubject%3A%20replace%20function%20changes%20the%20format%20of%20the%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1716489%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F809943%22%20target%3D%22_blank%22%3E%40berk81%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22alt-edited%22%3ETry%20it%20with%20this%20formula%3C%2FSPAN%3E%3C%2FSPAN%3E%20and%20then%20copy%2Fpaste%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3DTRIM(TEXT())%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

The replace function is changing the format of cells in certain situations.

 

I have a cell that is formatted as text and contains the value "01-2000   "  (3 spaces at the end)  It is a code and not a date.  I would like to remove the spaces at the end so I used the find and replace function and in the Find What: field, I put a space and leave the Replace With: field blank.

 

I click on replace all, the 3 spaces are gone but my cell now is now showing "Jan-00" and formatted as a custom field with mmm-yy.

 

I am able to use the trim() function and remove the spaces and copy and paste the value in a new cell but why is the replace function changing the format of a cell? Is there a way to prevent this?

 

Thanks, M

2 Replies

@berk81 

 

Try it with this formula and then copy/paste


=TRIM(TEXT())

 

I would be happy to know if I could help.

 

Nikolino
I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@Nikolino 

 

Thanks, I was able to use the trim function to accomplish what I want to do.  However, I am just puzzled as to why the replace function changes the format type of the cell.

 

M.