Convert complex text format to TEXT STRING?

I have a sheet that is a kind of inventory, where there are many categories of item. Each line of the inventory has a different embedded Custom Format in the form:

"("0") Type of Item, in Color X"

So when you type a number in that cell, say 5, it displays:

(5) Type of Item, in Color X

But it is a number, not a text string, so you can multiply it or add them or whatever you need to do. If you make another cell = to that cell, you get the numerical value, as it should be.

My question is this - is there a way to take any cell with a complex format involving characters like this, copy it, and paste it as a TEXT STRING? Or use a function or a VBA routine to capture the whole thing as a text string?

Using my example above that would mean being able to create a text string in another cell that reads "(5) Type of Item, in Color X"

I realize you will be tempted to offer suggestions about another way to organize the spreadsheet. I understand that.
But, please, I really do want to know the answer to this question of essentially converting formatted numbers to text strings for general knowledge and future reference.

Thanks!

Hmmm. Thanks. I'll give it a try.

Brilliant. Works.
Thanks.

This would be a custom number format. There's no worksheet function which returns a cell's full number format. CELL("format") is stuck mimicking the functionality of Lotus 1-2-3 Release 2.x from the mid-1980s, so it's not useful for this. You could use the XLM function GET.CELL in defined names, but that's inflexible.

Which leaves VBA as the best means for accessing a cell's .Text property. Note: changing number formats does NOT trigger recalculation at all, so even making user-defined functions (UDFs) volatile won't make formulas recalculate when number formats change. Meaning if you want to use such UDFs, you're going to need to develop the habit of pressing [F9] early & often.

Anyway, as a UDF,

Please Login or Register  to view this content.

The formula =gettext(X99) should return exactly what's displayed in cell X99 except for colors and font effects like boldface, italics, font size.