General discussion

Locked

Range to text conversion

By dennis.irvine ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Range to text conversion

by Zalog In reply to Range to text conversion

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.

Collapse -

Range to text conversion

by dennis.irvine In reply to Range to text conversion

Poster rated this answer

Collapse -

Range to text conversion

by SanKulPune In reply to Range to text conversion

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"

Collapse -

Range to text conversion

by dennis.irvine In reply to Range to text conversion

Poster rated this answer

Collapse -

Range to text conversion

by dennis.irvine In reply to Range to text conversion

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums