如何将csv字符串转换为列并在Excel中按特定关键字过滤？

I am trying to sum all the out(OUT) times from a csv string and show it in the next cell but is facing challenges. I am not able to process/split csv string. Need help on how to achieve this by excel formula or vba script.

我试图从csv字符串中总结所有out(OUT)时间并在下一个单元格中显示它但面临挑战。我无法处理/拆分csv字符串。需要有关如何通过excel公式或vba脚本实现此目的的帮助。

csv string:

10:06:in(IN),11:36:out(OUT),11:42:in(IN),13:57:out(OUT),14:05:in(IN),14:23:out(OUT),14:38:in(IN),16:39:out(OUT),16:49:in(IN),17:19:out(OUT),17:28:in(IN),17:54:out(OUT),17:56:in(IN),18:08:in(IN),18:08:out(OUT),18:11:in(IN),18:12:out(OUT),18:21:out(OUT),18:24:in(IN),18:37:in(IN),18:37:out(OUT),18:57:out(OUT),18:58:in(IN),19:26:out(OUT),19:35:in(IN),20:18:out(OUT),

The string is from one cell.

字符串来自一个单元格。

2 个解决方案

#1

Consider the following UDF:

考虑以下UDF:

Public Function SumOutTimes(rin As Range) As Date
    Dim Kount As Long, OutTimes(), t As String
    t = rin.Text
    ary = Split(t, ",")
    Kount = 1

    For Each a In ary
        If InStr(1, a, "out") > 0 Then
                ReDim Preserve OutTimes(1 To Kount)
                OutTimes(Kount) = Replace(a, ":out(OUT)", "")
                Kount = Kount + 1
        End If
    Next a

    For Each a In OutTimes
        SumOutTimes = SumOutTimes + TimeValue(a)
    Next a

End Function

For your data in cell A1

对于单元格A1中的数据

EDIT#1:

User Defined Functions (UDFs) are very easy to install and use:

用户定义函数(UDF)非常易于安装和使用:

1. ALT-F11 brings up the VBE window

ALT-F11调出VBE窗口

2. ALT-I ALT-M opens a fresh module

ALT-I ALT-M打开一个新模块

3. paste the stuff in and close the VBE window

粘贴内容并关闭VBE窗口

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

如果保存工作簿,UDF将随之保存。如果您在2003年之后使用的是Excel版本,则必须将文件另存为.xlsm而不是.xlsx

To remove the UDF:

要删除UDF:

1. bring up the VBE window as above

如上所述调出VBE窗口

2. clear the code out

清除代码

3. close the VBE window

关闭VBE窗口

To use the UDF from Excel:

要从Excel使用UDF:

=SumOutTimes(A1)

To learn more about macros in general, see:

要了解有关宏的更多信息,请参阅:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

有关UDF的详细信息,请参阅:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

必须启用宏才能使其正常工作!

#2

This may help you :

这可能对您有所帮助:

Sub test()

Dim Tx As String, A() As String, R()

Tx = Sheets("test").Cells(1, 1)
ReDim R(0)
A = Split(Tx, ",")

For i = LBound(A) To UBound(A)
    If Left(Right(A(i), 4), 2) <> "OU" Then
        'IN times
    Else
        'OUT times
        R(UBound(R)) = Left(A(i), 5)
        ReDim Preserve R(UBound(R) + 1)
    End If
Next i

ReDim Preserve R(UBound(R) - 1)

For i = LBound(R) To UBound(R)
    '---------------------------------------
    '------You can sum your times here------
    '---------------------------------------
Next i


End Sub

You juste have to read the CSV string and put it into Tx variable and choose how to sum your times!

你应该读取CSV字符串并将其放入Tx变量并选择如何总结你的时间!