remove and replace digits from a number

[Guest]

I am having some problems with data from one system being exported to excel.
the data I get from the system is translated by excel into another format and

it is supposed to come out as 2/1 but instead in interpreted as feb-01. I
can't change the format in order to get the correct value, so I think I need
a function for it.
I can re-format the value to 1999-02-01 but them, I need a function to
remove the first 5 digits and replace the (-) with a (/).

can anybody help me?

 

[Guest]

Use this formula to empty cell and copy and paste as value to original cell.
H17 is a cell which contains 1999-02-01

=DATE(,MID(H17,6,1),MID(H17,8,1))

 

[Guest]

try:

=SUBSTITUTE(RIGHT(TEXT(A1,"yyyy-mm-dd"),5),"-","/")

 

[Dave Peterson]

How about trying something else?

Tools|Options|Transition Tab|Check Transition Formula Entry
Then do the Edit|Paste
When I did this, I ended up with a formula: =2/1

Then I could select the range and
Edit|Replace
what: =
with: (leave blank)
replace all

Then Tools|Options|Transition Tab|Uncheck Transition Formula Entry

It may work ok for you.