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

Download:

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

    '-1

    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

    Next

 

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

End Function

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

 

 

Mobile
»
Errorcode 450 Using Email.Session.Accounts Email_From
»
Open and Read a Word File with Excel
»
Excel: REwrite RTF to HTML
»
Excel: Convert numbers, date and time to text
»
Excel: Convert letters to Ascii numbers around
»
Excel File Dialog for Selecting File-Paths to Worksheet
»
Excel Template: Automatically send an e-mail for a specific entry in Excel
»
Excel vba: recognize an input in cells
»
Excel, vba: insert image into a cell of cache
»
Excel: Formelfelder per Code kopieren

.

Contact for Jobs, Project Requests: raimund.popp@microsoft-programmierer.de