UDF to Convert formula results to text

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Can a UDF be used to convert formula results from column A to text in
column B?

I know that I can manually cut and paste "special" to achieve this, but I
want to avoid a manual step.
 
Instead of a UDF use a macro. Change columns(2) to columns(1) to change the
column withOUT the need for an additional column.

Sub converttovalues()
Columns(2).Value = Columns(1).Value
End Sub
 
There may be more than one way to answer your question depending on what
your ultimate goal is; so, what are you going to do with the text values in
Column B (that you can't do with the values already in Column A)?
 
Don said:
Can a UDF be used to convert formula results
from column A to text in column B?
Click to expand...

Would this satisfy your needs:

=text(A1, "0.00")

where "0.00" can be any numeric format you wish. Set the horizontal
alignment format to "right", if that is what you want.

If that does not satisfy your needs, please explain how your requirements
are different.


----- original message -----
 
PS....
=text(A1, "0.00")
Click to expand...

I wrote that because you asked for "a UDF ... to convert ... to text". I
wanted to show that no UDF is needed.

On the other hand, you also said that copy-and-paste-special-value achieves
what you want.

But PSV results in a numeric constant, not text per se. If that is what you
want, then:

1. A formula, like mine above or any other that might invoke a UDF, is not
what you want. And

2. No, you cannot do that with a UDF per se. A UDF cannot modify another
cell. You would need a macro.

But why use a macro when copy-and-paste-special-value would do the trick?

You mentioned that you want to avoid the manual step. So I wonder: are you
looking for an event macro, something that will effectively perform the PSV
whenever a cell is re-evaluated?

I suggest that you describe the original problem that you are trying to
solve by effectively doing PSV. The best solution for you might be a
completely different approach.


----- original message -----
 
Thanks for all the input guys.

I have a 240 column by 240 row matrix of stock symbols. At each intersection
I compute statistics based on the stock price. I then unwind this matrix
into a 57,600 row column that contains the computed statistic and the two
stock symbols. Each row looks something like this:

0.8567438 msft/csco

1. I need to sort the data.
2. I need to break apart the data into three columns ( 1 numeric and two
alpha).
3. I need to eliminate duplicate entries of which there are thousands.
4. I then plan to link the data into another workbook

I'm not yet sure what order the above needs to be done. Perhaps some of the
above could be accomplished on a formula, but I don't think all of it can.
I'll probably be running the workbooks everyday so I want as little manual
intervention as possible.

I'll probably end up using a macro. If I get ambitious maybe I'll make it
event driven.
 
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

BTW. I am a retired RM for ING and held a series 7
 
Back
Top