Convert Concatenated Stuff Back to Text Format

[dgr]

Hello,
I am using Excel 2007 running on Windows 7.

There are =CONCATENATE() formulas anywhere in column C. I want to find these CONCATENATE formulas & convert the concatenated stuff to 'normal' text. I'm using a crude code which does the job but it's too slow as it involves copy & paste. My crude code is below:-

Sub paste_special_full_add()
'copies the concatenation & pastes special it back in column c without the formatting
Dim c As Range
For Each c In ActiveSheet.Range("C:C")
c.Copy
If c.Value <> "" Then c.PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
Next c
End Sub

Could you propose a more efficient code please?

Alternatively, I am using the following code to find for =TODAY() formulas & converting these formulas to 'normal' text format. Could you show me how to modify this code so that I can use it to find =CONCATENATE() & convert the concatenated stuff to 'normal' text please?

Sub DateToString()
    Dim szDate As String, c As Range
     
        On Error Resume Next
        For Each c In Columns("B:B").SpecialCells(xlCellTypeFormulas)
            If (IsDate(c.Value)) Then
                szDate = Format(c.Value, "yyyy-mm-dd")
                c.NumberFormat = "@"
                c.Value = szDate
            End If
        Next c
End Sub

Thanks a lot

 

[rallcorn]

Try this approach, which is the same as copying & pasting special/values:

Sub ColCtoVal()
Range("C:C").Value = Range("C:C").Value
End Sub

 

[dgr]

Thanks a lot rallcon for teaching me this new trick. I appreciate your help. The code works fine

 

[rallcorn]

Happy to pass along what I learned from someone else!

 

[Rick Rothstein]

You may find this mini-blog article of mine of some interest for handling the concatenation of formatted text values...

[h=3]FORMATTED Flexible Concatenation Function[/h]