Using formula to convert date result to text

Hello,

I used a formula to convert text into a specific date value. Now I would like a formula to convert that result into text. I understand that you can do a past special and only paste the values, but I am wondering if there is a formula to do this so I do not have to constantly re-copy and re-paste when I make changes to the original text strings.

Thank you

Originally Posted by hbcobra

Hello,

I used a formula to convert text into a specific date value. Now I would like a formula to convert that result into text. I understand that you can do a past special and only paste the values, but I am wondering if there is a formula to do this so I do not have to constantly re-copy and re-paste when I make changes to the original text strings.

Thank you

I think you need to explain a little more.
What was the value before your current formula did its magic? What is the current formula? What is your desired result?

Originally Posted by Spencer101

I think you need to explain a little more.
What was the value before your current formula did its magic? What is the current formula? What is your desired result?

Here is a link to a sample .xlsx I am using on Dropbox:

http://dl.dropbox.com/u/1185462/Sample.xlsx

Currently I am copying the data in column B and pasting it as a value into column A. It would be great if I could use a formula to get a static text value in column A since my original column E values are going to change quite often.

In addition, is there a way to trim the "CAL" and the ".PDF" in a single formula? Currently I have it as two separate formulas (columns C and D).

Thank you very much.

Originally Posted by hbcobra

Here is a link to a sample .xlsx I am using on Dropbox:

http://dl.dropbox.com/u/1185462/Sample.xlsx

Currently I am copying the data in column B and pasting it as a value into column A. It would be great if I could use a formula to get a static text value in column A since my original column E values are going to change quite often.

In addition, is there a way to trim the "CAL" and the ".PDF" in a single formula? Currently I have it as two separate formulas (columns C and D).

Thank you very much.

Enter =MID(E2,4,6) in C2 of your example and copy down. You then don't need column D so it can be deleted. That's your second query dealt with.

I'm still not sure I get what you mean for the first one though. Why do you need to convert the dates to a static value?

Originally Posted by Spencer101

I'm still not sure I get what you mean for the first one though. Why do you need to convert the dates to a static value?

I will be exporting the data in column A to another file. When I try to export the contents of column B, I get an error. For whatever reason, I do not seem to get that error with static values.

Originally Posted by Spencer101

Enter =MID(E2,4,6) in C2 of your example and copy down. You then don't need column D so it can be deleted. That's your second query dealt with.

Perfect, thank you

> I used a formula to convert text into a specific date value. Now I
> would like a formula to convert that result into text.

The TEXT function may provide what you need.

With a date in A1, try
=TEXT(A1,"Mmm dd, yyyy")
or
=TEXT(A1,"mm/dd/yy")
or
=TEXT(A1,"Mmmm d, yyyy")