Discussion Locked

Range to text conversion

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is there a way to convert a reference to a range of cells into a text string.

In other words, given an Excel Function that returns a reference to a range,can that range address be converted to a text string?

Dennis Irvine

5 replies

by zalog

You can do this using a two step process: 1) create a new range using the =text() function for each cell in the range. 2) select the new range and from the menu choose Edit, Copy. Now from the menu choose Edit, Paste Special and change the paste option from "All" to "Values" and click ok. This will replace the formulas with the text strings of the values.

by sankulpune

Hello Dennis,

The 'address' property (VBA for Excel 2000) of a range object returns the range as string. Following example will clarify.

1) Open a worksheet and in the VBE (VBA Editor) paste the following code:
Sub rng2txt()

Dim rng As Range

Set rng = Selection
txt = rng.Address

End Sub
2) Go back to worksheet, select a range.
3) Return to VBE and run the code.

'txt' returns the range as string which you can process further.

Hope this helps.
Please do not hesitate to contact me if you need clarifications / more info.

With regards,

SanganakSakha
'Effective and Simple Solutions To Small Problems, No QuickFixes"

This discussion is closed to new replies.