Restrict an Excel column to only be able to copy values with text format-CodePudding

I have a "NUMERICAL" column which is the source column where I am going to copy the data and then I have a "TEXT" column which is the destination column where I am going to paste the data that I have copied.

As you can see in the image, the numbers column is in numeric format and the text column is in text format.

Also, to column A ("TEXT") I have put a data validation (the one that appears in the image), as I understand it, this validation is going to give me an error if I try to put something that is not text.

Now I want to paste the number that is in the numbers column, the cell is in numerical format and I am going to copy with ctrl c and paste with ctrl v in the empty underlined cell, which, as seen in the image, already has text format and it also has the validation

When I do this, the format of the cell where I want to paste the value changes to numeric and it ignores the validation that I have done and everything. What could I do to force the values entered in this cell to always have a text format origin?

If someone can help me with this I would be eternally grateful, I hope I have explained myself well.

CodePudding user response：

Please, copy the next code in the respective sheet code module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.column = 1 And Target.Columns.count = 1 Then
        Application.EnableEvents = False
            Target.TextToColumns Target, , , , , , , , , , Array(1, 2)
        Application.EnableEvents = True
    End If
End Sub

It works even if you copy more such cells, but only if the copied range has a single column. I think, if necessary, the code can be adapted to also accept more columns to be copied, but format as text only the range first column. Not much more complicated...

If something not clear, please do not hesitate to ask for clarifications.