Converting a range values from number to text with vba

[mahhdy]

Hello,

I have a huge list, I want to change my reference columns data from number to text. I don't want to add any new column and for instance, use text() function. I am just wondering if it is possible to do that in place with a VBA code or not. I tried

selection=selection.text

and converting to array back and forth but not succeded. Any idea? I also don't want to do a loop as I need to this effort for several files which I can't manipulate their structure.

Regards,
M

 

[Qadah]

Hi all

This is an old thread but a question, if i want to convert the range values from numbers to text, BUT preserve all different formats found in the range per cell

All of these (except for the Hi) are exactly the same number: 1.0011123123 but each formatted separately, i need to convert the range into text, and preserve all, including the %, etc. is this possible with VBA ? i know how to do this using a very, very long formula that gets cell("format",ref) then replace the output (lookup) from a table with the text formula corresponding format:

For example, in the range i might have

								value types
Original number with format	100.11%​	1.0011123​	1.00​	Hi	$1.00​		P2	0#.00%
Cell Reference result	P2	G	,2	G	C2		G
=IF(C10="G",C9,TEXT(C9,VLOOKUP(C10,$I$9:$J$13,2,FALSE)))	100.11%	1.0011123​	1.00	Hi	$1.00		,2	#.00
							G
							C2	$#,##0.00

the first row contains the original cells , the only solution i can think of (vba aside) is to get cell format, and have a lookup table ready with all possible cell format, then apply text function to convert the results in the my destination to text using helper cells and the lookup table

please tell me there is an easier way

Thanks

 

[Rick Rothstein]

Assuming you are selecting the cells (on a single row like your example shows) to copy and that the text copy of them will be placed in the cell below, does this do what you want...

Sub CopyAsText()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Offset(1).NumberFormat = "@"
    Cell.Offset(1).Value = Cell.Text
  Next
End Sub