如何以相反的顺序显示子字符串，但在VBA中从左到右读取 [英] How to display sub string in reverse order but read from left to right in VBA

问题描述

我正在VBA中处理.txt文件.

I am processing a .txt file in VBA.

在其他任务中，我需要读取表示日期的字符串并在Excel中显示实际日期.

Amongst other tasks, I need to read in a string representing a date and display the actual date in Excel.

.txt文件中的日期字符串看起来像"190223"这代表23/02/2019

A date string in the .txt file looks like "190223" This represents 23/02/2019

我的挑战是完成这项任务.

My challenge is to get this done.

到目前为止，我所做的是:

What I have done so far is:

' ... loop

With ActiveWorkbook.Worksheets(1)

' Other statements here

' Event date time
    .Range("N" & i).Value = StrReverse(Mid(.Range(keyword.Offset(0, 4).Address), 1, 2) & _
    "/" & Mid(.Range(keyword.Offset(0, 4).Address), 3, 2) & _ 
    "/" & Mid(.Range(keyword.Offset(0, 4).Address), 5, 2))

End With

但是我得到了不想要的输出:

But I get the undesired output:

32/20/91 ' For a date string 190223 the desired output should be 23/02/19

任何帮助将不胜感激.

谢谢.

推荐答案

将其转换为真实日期

您必须提取该字符串的年，月和日，然后将其转换为实际日期.

Convert it into a real date

You must extract year, month and day of that string and then convert this into a real date.

然后，您可以将日期格式化为所需的任何日期格式.这样，保存在单元格中的值就是一个实际的日期值(不是字符串！)，因此您可以使用它进行计算.

Then you can format the date to what ever date format you like. The value that is saved in the cell is then a real date value (not a string!) so you can calculate with it.

我强烈建议您阅读日期如何在Excel中工作–日历系统说明+视频了解背景以及为什么真实日期如此重要.

I highly recommend to read How Dates Work in Excel – The Calendar System Explained + Video to understand the background and why real dates are so important.

这里是一个例子:

Option Explicit

Public Sub ConvertDateExample()
    Const InputStr As String = "190223"

    Dim InputYear As Integer
    Dim InputMonth As Integer
    Dim InputDay As Integer

    'extract year, month and day
    InputYear = Left(InputStr, 2)
    InputMonth = Mid(InputStr, 3, 2)
    InputDay = Right(InputStr, 2)

    'put it together to a real date
    Dim RealDate As Date
    RealDate = DateSerial(InputYear, InputMonth, InputDay)

    'write the date into a cell
    Range("A1").Value = RealDate
    'format that cell to your desired format
    Range("A1").NumberFormat = "dd/mm/yyyy"
End Sub

这篇关于如何以相反的顺序显示子字符串，但在VBA中从左到右读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！