excel file There are many values ​​in the column. Cells with different formats.

Suppose the value in cell 9, and it is displayed as 9C-228TSP , because the cell has the format 0"С-228ТСП" . There are a lot of similar formats.

How to copy values ​​in the way they are displayed (a number with text)?

The TEXT function is not suitable, since there are a lot of formats, each cell has its own

enter image description here

  • Formatting is preserved when copying the standard method. It is not clear what the snag is - Denis E
  • In order to continue working with this cell, it is necessary that it be a text - Sergey Chumakov
  • If as an option to make a copy of a column, format it already, and work with it as well? - Denis E
  • Sorry, I did not understand. Even suppose cntl + f does not work, because in cell "9" and not 9C-228ТСП. The cell with the value "9", formatted with the additional "C-228TSP" - Sergey Chumakov
  • Data needs to be changed on the spot7 Move to another range? Perform other actions? - vikttur

1 answer 1

If all formats have the same structure (the number "text" ), execute a small macro:

 Sub ValueInFormat() Dim rRng As Range Dim i As Long With Worksheets("Лист1") i = .Cells(.Rows.Count, "B").End(xlUp).Row Set rRng = .Range("B1:B" & i) End With For i = 1 To rRng.Rows.Count With rRng(i, 1) If .Value = "" Then .Value = .Value & Split(.NumberFormat & """", """")(1) End With Next i rRng.NumberFormat = "@" Set rRng = Nothing End Sub

The code changes the values ​​of the cells in the filled range in column B and sets the text format to the cells.

A little refinement - the source data will not change, the result will be recorded in a separate range:

 Sub ValueInFormat() Dim aF() Dim rRng As Range Dim i As Long With Worksheets("Лист1") i = .Cells(.Rows.Count, "B").End(xlUp).Row Set rRng = .Range("B1:B" & i) aF = .Range("B1:B" & i).Value For i = 1 To UBound(aF) If aF(i, 1) <> Empty Then aF(i, 1) = aF(i, 1) & Split(rRng(i, 1).NumberFormat & """", """")(1) Next i .Range("D1").Resize(UBound(aF), 1).Value = aF End With Set rRng = Nothing End Sub

Code to place in the general module. VBA Editor Login - Alt+F11

  • aF (i, 1) = aF (i, 1) & Split (rRng (i, 1) .NumberFormat, "" "") (1) the line causes an error - Sergey Chumakov
  • Either process the wrong sheet (in the macro the name "Sheet1" is specified), or in the cell another type of formatting. If an error occurs, place the pointer over the variable i - show the value of the variable. In this case, the line number. Look at the formatting of the cell in the row i - vikttur
  • It shows line 415, there are already normal cell formats, tried to delete everything below 415 lines, it still gives an error, now on an empty cell. Added the file itself to the cap. - Sergey Chumakov
  • Changed the line of code (ignoring empty cells and bypassing an error with other formatting) - vikttur