Forum Discussion

berk81's avatar
berk81
Copper Contributor
Sep 26, 2020

replace function changes the format of the cell

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

excel

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 26, 2020

    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.

    • berk81's avatar
      berk81
      Copper Contributor
      Sep 26, 2020

      NikolinoDE 

       

      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.

Resources