Looking for a formula which copies its result to another cell as text

[Talmadge]

Hello everyone

I've been using Excel for my entire IT career (20+ years) but never in the way I have been using it since I started my current position 3 months ago. In all those years I only just scratched the surface, and never knew what all Excel could do. I didn't even have a clue to how ignorant I was. (2 weeks ago I did my first Pivot table, with help from chandoo org Thanks )

I've searched for this a few times, but I either am am not using the proper terminology or i'm looking at the problem wrong. Now that I know there are classes on this site I will be taking them at night.

So here it is:

Lets say you have a simple spreadsheet. A1 has the value 10. B1 has the value of 1. C1 has a formula, =A1-B1. That's all fine and good but here is my question. Is there a way to write that formula so that the result not only shows in the C1 cell, but also the TEXT is copied over into D1? I know, this can be done manually, copy then paste values, but is there a way to do this inside the formula so you don't have to go back and run a macro or copy and paste.

Please let me know if anyone knows the formula for that, or if I asked an impossibility

Thanks for your help, and thank you all for this wonderful site.

 

[Luke M]

Hi, and welcome to the forum!

With native XL, short answer is no. A cell can either have a static value, or a formula, not both. We could mimic your desired effect using a short VB macro...but at the root of it, may be better to ask why?

If you're like me and need to paste values a lot, I'd recommend adding that button to your QAT. I have mine in the second slot, so now whenever I need to paste values, I just hit Alt+2 (or you can remember the native sequence, Alt+e,s,v,enter)

 

[Talmadge]

Hi, and welcome to the forum!

With native XL, short answer is no. A cell can either have a static value, or a formula, not both. We could mimic your desired effect using a short VB macro...but at the root of it, may be better to ask why?

If you're like me and need to paste values a lot, I'd recommend adding that button to your QAT. I have mine in the second slot, so now whenever I need to paste values, I just hit Alt+2 (or you can remember the native sequence, Alt+e,s,v,enter)

Sad, Excel can do so many awesome things, and a few seemingly straightforward things it cannot. But thank you for getting back to me Luke.

You asked why I was looking for this function...
Its come up over the years a few times, I can't remember what those scenarios were but I know the current issue.

So I have a workbook
2 columns in it contain date fields (formatted m/d/yy h:mm)
One is a record creation date, and the other is the records approval date.
Column AY has the simple formula =AP2-AO2 so it subtracts those values.
I have the custom format ([h]:mm) on those cells (something I think I picked up from this site)
This gives me the hours and minutes from when the record was created until when it was approved, and works fine, for most cells
However, some of the records have either blank start or approved times (dirty data from the export) or have identical time stamps for both.
These show up as 0:00 or - numbers (####)

That worksheet feeds a pivot table.
The purpose of that table was to be able to show the categories of different records and how long it takes to get them approved.
Now remember, the number the formula returns looks correct in the main worksheet (h:mm), however in the pivot table brings the numbers in as a date.
For example the top record has a time calculated value of "2375:42" but the pivot table shows this as "4/7/1900 23:42"
So I format the column on the pivot table, then try to sort, but am unable to.
I click on the pull down filter button on the column, and I want to sort "descending", but the only option I have is for "A to Z"

Thinking it through to put it into words for a stranger has made me look at this differently.
Is the root of my problem the dirty data? Do I have fields mixed in with the numbers, and so, excel can't do the sort?
I guess I can try a filter which removes all the bad data and try it again.
I was hoping it would just sort and the junk would rise up or fall to the bottom.

Any other thoughts?

(ps)
As I jumped back to do the sort I noticed that I not only had - numbers in some, but some came over as time stamps, mixed with the ones which show as h:mm
its weird because when I go back to the original data and do a sort on the calculated field which feeds that column on the pivot table, everything looks like its in the proper h:mm format?!?!? I think that's why I was looking for a way to paste text only into another field, so I could build the pivot table off that new column, and avoid any conversion from the formula field to the pivot table.

 

[NARAYANK991]

Hi ,

As they say , a picture is worth a thousand words. Uploading your workbook takes a few seconds , and gives a clearer picture than all the words you may use.

Probably the simplest solution may be a number format of [h]:mm:ss , but just from your description , it is difficult to say.

Narayan

 

[eibi]

Talmadge,

Welcome to the forums.

To go back to your initial question, there is a formula you can put in cell D1 to convert the value in cell C1 to text:

=TEXT(C1,"h:mm")

I don't know how this will affect the pivot table problems you are having, but this formula is specifically to convert a number to a text value as you requested initially.

All best.

 

[Luke M]

Sounds like you're on a good track OP, investigating the data. I have suspicions that you'll find something wrong there. If not, as Narayan said, upload a sample, and we'll help you investigate.

 

[Talmadge]

Sorry for not getting back sooner, really busy here.
I need to clean the data (private info in the workbook) before I can attach
I will try to get back to it later today and upload
thanks for looking into this guys

 

[Talmadge]

So, in a nut shell, if you look at the attached xlsx, Sheet1, is there a way to group the "day of the week - date created" by day, yet retain the DDDD format so it says the name of the day of the week?


Thanks for your comment Eibi, but it's not what I was looking for. That DOES convert the value to text, but there is still a formula in the cell and not JUST the text. It was my ignorance asking for the "paste the text into another cell" answer. See comments below, I have seen the error in my logic.



Sorry this took so long, I had to garble the corporate data before sending the attached file.

In creating this small subset sample file and retracing my steps I believe i didn't understand the problem before, here is a hopefully more enlightened version of my problem (now with xlsx goodness inside)

So let me restate the root of my problem and the specifics.
I have records in a table
These each have a unique identifier
They also have a few dates attached to each record
At some point I made a field which copies the date field (column f) with a simple =CellNumber formula (in column g)
This gives me a copy of the date, but this column is format/custom to be DDDD
(so it gives the full name of the day of the week)
This works in the spreadsheet but fails when I put it into a pivot table
(this piece was missing from my original post, I only understood what the problem was when I made the attached sheet)
OK, so you make a pivot table (in my attached file, tab "sheet1"
You put the record id under row labels
Then you put the "day of the week - Created date" under column labels
To my surprise this worked, even though it failed a bunch of times in my original file
but then I realized what causes the problem
The next think I do is group by date on the date field, to keep the pivot table smaller and readable
However this changes the nice "Day of the week" names into calendar dates ("Tuesday" becomes "Jan-6")
This can be seen on "Sheet2"
I also added the date field on Sheet2, its the original data, of which, the day of the week field is copied from
My intention was to group on this, and not care if the displayed text got converted, because the day of the week is also present right under it.
However when you group the date field it does not group the data under it

I moved the "in a nutshell" comment to the top of this long winded post, sorry was writing as I went to tell the story.

***Off topic***

If your a nube (like me) there may be some other useful formulas in the attached sheet.
Like I stated above I used = and then a cell number to auto copy the contents of one cell to another, so I could use some cool date formats
(If you want to use these right click on a date cell and choose "format cells", then select "custom" at the bottom of the list. Type in one of the following)

dddd to give you the full name of the day of the week
d or dd which gives you the day of the month (in 1 or 2 digits (with leading 0)


Towards the end there are a bunch of =MID/FIND formulas used to tease out chunks of text from inside the larger text of another cell

Column M uses =MID(I2,FIND("RC=",I2)+0,15)
Which looks at cell I2, and does Find to get the text "RC=" from that position inside the cell it starts copying the text at the 0 position (could be increased if you wanted to skip a few letters/numbers. And the last 15 indicates it only grabs 15 characters to show in the cell where the formula is

Columns P,R,S, and T all run variations on that same formula
I hope this can help someone
It may be old hat to some of you, but it blew my mind when I got that formula working

 

[Talmadge]

Sounds like you're on a good track OP, investigating the data. I have suspicions that you'll find something wrong there. If not, as Narayan said, upload a sample, and we'll help you investigate.

I went back and pulled an older "working" version of this sheet
On that one I had actually copied out the date cell (formated dddd) and pasted into notepad, then copied it back out and pasted over the values so that it was just plain text.

That process seems to work, but I would like to avoid that.
There must be a better way. (please see my last update for the attachment)

 

[Talmadge]

Yeah, thats it, just tested again with the file I attached earler
If I take column G (due day of the week) copy it out and paste into notepad
then copy from notepad and past back to overwrite column G, (and refresh the pivot table) it works.
You can group, by day, and when it collapses down the day names are written out

so how do I make the pivot table do that without having to copy and paste to convert the cells from a formula into text

 

[Luke M]

Ah, the mystery becomes clear now.

In your file, the column that is currently figuring out Day of the week...instead of a basic link formula, e.g.,
=D2
make it:
=TEXT(D2,"DDDD")

This will convert the number date into a string. As you may quickly surmise, this has the benefit then of only being able to give 7 different choices into your PT, and things move much closer to your desired outcome.

I also see that you've got something similar with the day of the month. Rather than having the value of the cell be a date, formatted to show only a number, we can extract just the date. I think the attached will be helpful.

 

[Talmadge]

Ah, the mystery becomes clear now.

In your file, the column that is currently figuring out Day of the week...instead of a basic link formula, e.g.,
=D2
make it:
=TEXT(D2,"DDDD")

This will convert the number date into a string. As you may quickly surmise, this has the benefit then of only being able to give 7 different choices into your PT, and things move much closer to your desired outcome.

I also see that you've got something similar with the day of the month. Rather than having the value of the cell be a date, formatted to show only a number, we can extract just the date. I think the attached will be helpful.

thanks i'll check it out
Looks like Eibi was on the right trail after all

Thanks guys I'll write back tomorrow with results

 

[Talmadge]

yep that does it

Thank you Eibi and Luke M!

 

[Talmadge]

wow, sorry, I never hit "post reply" and just saw this never went out.

Is there any mechanism to mark this "solved" or give credit?
(I gave you both "likes")

 

[Luke M]

No worries Talmadge, thanks for following up. (A like is good enough).