Freelance Project Requests Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net


Datei 1: Excel_Umwandeln_Zahlen_Datum_in_Text.xlsm



How to convert entire columns or ranges in Excel from numbers, date or time format to real text.




In vba macro

Version 1: Convert numbers to text


You can simply convert areas to Vba with the Range.TextToColumns  function.

Numbers are certainly converted to text.

However, the date error occurs here, that they are displayed in american date time format. When operating via the menu bar, the conversion is correct only at the vba code level the error occurs

Option Explicit On


Private Sub Korrektur_Zahlen_in_Text_Umwandeln()

    '--------< Korrektur_Zahlen_in_Text_Umwandeln() >---------

    '*wandelt die SAP Spalten Personalnummer und Abteilungen in Text um, damit die SVerweise und Vergleiche keine Fehlercode 13, Typumwandlung #NV erzeugen


    Convert_Range_From_Number_To_Text Worksheets("Tabelle1").Range("A:A") 'Mitarbeiter


    '--------</ Korrektur_Zahlen_in_Text_Umwandeln() >---------

End Sub

'==========< Convert To Text >==================


Private Function Convert_Range_From_Number_To_Text(ByRef cells_Range)

    '--------< Convert_Range_Number_To_Text(Range) >---------

    '*converts a cell-Range from numbers to text

    cells_Range.TextToColumns DataType:=xlDelimited,

       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,

       Semicolon:=False, Comma:=False, Space:=False, Other:=False,

       FieldInfo:=Array(1, xlTextFormat), TrailingMinusNumbers:=True


        'FieldInfo:=Array(1, 2)

    '*parameter 2

    'Const xlDMYFormat = 4

    'Const xlGeneralFormat = 1

    'Const xlMDYFormat = 3

    'Const xlMYDFormat = 6

    'Const xlSkipColumn = 9

    'Const xlTextFormat = 2

    'Const xlYDMFormat = 8

    'Const xlYMDFormat = 5


    '--------</ Convert_Range_Number_To_Text(Range) >---------

End Function

'==========</ Convert To Text >==================





Version 2: Convert von date and time andcomplex cells


With the following function you can safely convert all possible cells from Excel into the same display as text.

This reads the text for each Excel cell, converts the format to text and then rewrote the text

sText = CStr(cell. text)  'cell. Value

Cell. NumberFormat = "A"

Cell. Value = sText




Vba code for converting date to text

Option Explicit On


Private Sub Korrektur_Datum_in_Text_Umwandeln()

    '--------< Korrektur_Datum_in_Text_Umwandeln() >---------

    '*wandelt die SAP Spalten Datum und Uhrzeit in Text keine Fehlercode 13, Typumwandlung #NV erzeugen

    Convert_Range_From_Date_To_Text Worksheets("Tabelle1").Range("D:D") 'Beginndatum der Mitarbeiterzeit

    Convert_Range_From_Date_To_Text Worksheets("Tabelle1").Range("E:E") 'Beendet um

    Convert_Range_From_Date_To_Text Worksheets("Tabelle1").Range("F:F") 'Beginn

    '--------</ Korrektur_Datum_in_Text_Umwandeln() >---------

End Sub



'==========< Convert To Text >==================




Private Function Convert_Range_From_Date_To_Text(ByRef cells_Range)

    '--------< Convert_Range_Number_To_Text(Range) >---------

    '*converts a cell-Range from Date and Time to text

    Dim cell As Range

    Dim sText As String

    For Each cell In cells_Range

        If Not cell.Value Like "" Then

            'cell.Value = DateValue(cell.Value)

            sText = CStr(cell.Text)     'cell.Value

            cell.NumberFormat = "@"

            cell.Value = sText

        End If



    '--------</ Convert_Range_From_Date_To_Text(Range) >---------

End Function

'==========</ Convert To Text >==================





Contact for Jobs, Project Requests: