Replace easy question
Im a column I have entries as follows:
6-16
5-10
2-14
I want to replace the "-" with "/"
When I go ahead and replace then using CTRL + H method; The replacement will also calculate a decimal number (42491) or put a date like entry (05/06/2009).
All I want is the cell to show 6/16 instead of 6-16.
I have changed the cell format to text and also have set the calculation to manual, but still keep having the same behaviour.
Any tips on how to do it? I'd really appreciate it.
ps. if i do copy the whole column to a text file, then replace the "-" with "/" in te text file itself and then paste the new data into the excel spreadsheet, it will work..... but this is not what I want. want to use only excel.
»
- adriano's blog
- Login or register to post comments
- 3846 reads
Prevent Excel Date Recognition
hihi
this is an annoying one... There's no way that I have found to prevent Excel from trying to interpret your data how you intend. There's no setting in the Excel options that turns this off.
If you have the ability to enter the data yourself, simply put ' before, and Excel will not interpret this, or format the column as text BEFORE entering the data.
If the data is already there, there are 2 ways to proceed.
1. Via Text file - You have discovered this.
2. Use the SUBSTITUTE formula to create your new text, format that column as text, and copy and paste special values.
e.g: =SUBSTITUTE(A1,"-","/")
Nick
Thanks Nick
The substitute formula works fine. :)