Pasting Values as Displayed Without Formatting-CodePudding

I have a column of cells in excel that have the following formatting: "0000.00" FYI, the quotes are not part of formatting.

Basically, four digits followed by two decimals. However, when the numbers are like "600", they need to be displayed as "0600.00". However, the list of numbers provided to me are displayed that way through formatting, so if I am trying to VLOOKUP, it can't process it; it sees "600", not "0600.00" that is displayed to me.

I am aware of PasteSpecial Paste:=xlPasteValues, but this pastes "600", not the "0600.00" that is displayed to me. Currently I can achieve such results by copying the values and pasting them into notepad —which suggests to me there is a way to do this— but I'd like to create a macro to do this for me.

Sorry for any redundant explanation, just wanted to avoid getting answers relating to pasting values only, which is not what I am looking for.

CodePudding user response：

As you said, to use VLOOKUP with formatted text as the lookup value, you'll need the value of the cell to match with the value of the lookup value, so you'll have to convert the value in the cell to text with something like this (exemple for a single cell):

Dim rng As Range
Set rng = Range("A1")

rng.PasteSpecial xlPasteFormulasAndNumberFormats

Dim TextValue As String
TextValue = Format(rng, rng.NumberFormat)

rng.NumberFormat = "@" 'We need this line to turn the cell content into text
rng.Value2 = TextValue

I'm pretty sure no PasteSpecial options will allow you to do what you want in a single operation, so this solution is a workaround that does it in two steps.

Mutliple cells case:

I realize that the code above doesn't address the issue of pasting multiple cells, so here's a procedure that can be used to copy the formatted number as text from one range to another:

Sub CopyAsFormattedText(ByRef SourceRange As Range, ByRef DestinationRange As Range)
   
    'Load values into an array
    Dim CellValues() As Variant
    CellValues = SourceRange.Value2
       
    'Transform values using number format from source range
    Dim i As Long, j As Long
    For i = 1 To UBound(CellValues, 1)
        For j = 1 To UBound(CellValues, 2)
            CellValues(i, j) = Format(CellValues(i, j), SourceRange.Cells(i, j).NumberFormat)
        Next j
    Next i
    
    'Paste to destination by using the top left cell and resizing the range to be the same size as the source range
    Dim TopLeftCell As Range
    Set TopLeftCell = DestinationRange.Cells(1, 1)
    
    Dim PasteRange As Range
    Set PasteRange = TopLeftCell.Resize(UBound(CellValues, 1), UBound(CellValues, 2))
    PasteRange.NumberFormat = "@" 'We need this line to turn the cells content into text
    PasteRange.Value2 = CellValues
    
End Sub

It's basically the same idea, but with a loop.

Note that if the formatting is always the same, you could make it a variable and apply it to every values in the array instead of calling .NumberFormat on every cell which inevitably adds a little bit of overhead.

Sidenote

One could ask why I'm not suggesting to use :

SourceRange.Cells(i, j).Text

instead of

Format(CellValues(i, j), SourceRange.Cells(i, j).NumberFormat)

And that would be a very good question! I guess, the fact that .Text can return "###..." when the column isn't sized properly always makes me affraid of using it, but it certainly would look much cleaner in the code. However, I'm not sure what would be better in terms of performance. (Relevant article by Charles Williams)