Can You Return Cell Formatting as a String in Excel? 3

[Fenrirshowl]

Is it possible (and easy) to convert custom formatted cells to text strings (including the formatting) which can be used in a mailmerge, say?

E.g. Custom format to the cell is "The value is: " & 0.00
The cell has 100 as it's value so the cell shows "The value is: 100.00" and this entire text is to be merged to a word document.

Unfortunately someone has already set up the spreadsheet using the formatting route rather than referring to cell values to create text via the '="The value is: " & A1' approach. (Apparently there is a need for the cell to show no text if empty, so the formatting route was taken).

I'm hoping for a shortcut rather than having to go through and set up a second set of cells with the text in.

 

[dallen43]

I'm rather a new at this but the following worked for me.

I started with A1 as following:
A1: value: 100 Format "& 0.00"

Then I put the following into B1:
B1: value: =TEXT(A1,"& 0.00")

I then copied B1 and Pasted Special, Value into C1.

This gave me the result:
C1: value: & 100.00 (which is text).

Hope this helps.

 

[StuckInTheMiddle]

You can easily get what you want to referring to the cells TEXT property rather than it's VALUE

range("a1").text would return the full formatted string where as range("a1").value returns just the value.

A,

 

[firefytr]

Check out the GET.CELL function (an old Excel4Macro technique) or use a UDF that makes use of StuckInTheMiddle's VBA solution. A UDF might look like this ...

Public Function GETVALUE2(celRef As Range) As Variant
    GETVALUE2 = celRef.Text
End Function

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

If you're doing a Mail Merge, you're kind if stuck with the VALUE that's in a cell.

If it's a NUMBER that gets FORMATTED with decimals, commas, dates with dashes or slashes etc, the VALUE must be changed

You could use a Macro like this...

Sub ChangeNumberToFormat()
    Dim r As Range
''SELECT the range you want to convert
    For Each r In Selection
        If IsNumeric(r.Value) Then
            r.Value = "'" & r.Text
        End If
    Next
End Sub

Skip,

_[red][/red]

 

[Fenrirshowl]

Thank you all for the advice. I think a bit of reorganisation of the spreadsheet and then joining A's or Zack's & then Skip's answers is my way forward so it can be updated in the future.

Thanks again.

Rich