从一堆数据当中导出特定字符后的数据

cexo790527

请教各位大佬，我从手机APP里面导入了房贷信息，里面是一堆连续文本，我想从里面将每个月的还贷金额导出，并排列到相应的格内。请问如何导出。期望格式为附图，具体文本如下：
￥2,074.032018-07-16 21:50还款+￥2,078.692018-06-16 21:44还款+￥2,083.352018-05-17 21:50还款+￥2,088.022018-04-16 21:47还款+￥2,092.682018-03-16 21:49还款+￥2,097.342018-02-16 21:06还款+￥2,102.002018-01-16 21:46还款+￥2,106.662017-12-16 21:41还款+￥2,111.332017-11-16 21:48还款+￥2,115.992017-10-16 21:44还款+￥2,120.652017-09-16 21:36还款+￥2,125.312017-08-16 21:41还款+￥2,129.972017-07-16 21:41还款+￥2,134.632017-06-16 21:39还款+￥2,139.302017-05-16 21:57还款+￥2,143.962017-04-16 21:46还款+￥2,148.622017-03-16 21:50还款+￥2,153.282017-02-16 21:46还款+￥2,157.942017-01-16 21:48还款+￥2,162.612016-12-16 21:41还款+￥2,167.272016-11-16 21:40还款+￥2,171.932016-10-16 21:56还款+￥2,176.592016-09-16 22:03还款+￥2,181.252016-08-16 22:04还款+￥2,185.912016-07-16 21:56还款+￥2,190.582016-06-16 21:59还款+￥2,195.242016-05-16 22:02还款+￥2,199.902016-04-16 21:51还款+￥2,204.562016-03-16 22:09还款+￥2,209.222016-02-16 22:02还款+￥2,350.652016-01-16 22:07还款+￥2,493.262015-12-16 22:10还款+￥2,499.112015-11-16 22:18还款+￥2,504.962015-10-16 21:55还款+￥2,510.812015-09-16 21:59还款+￥2,516.662015-08-16 21:45还款+￥2,522.522015-07-16 21:52还款+￥2,528.372015-06-16 21:56还款+￥2,534.222015-05-16 21:51还款+￥2,540.072015-04-16 21:58还款+￥2,545.922015-03-16 22:00

wp231957

=HSTACK(RIGHT(TOCOL(TEXTBEFORE(TEXTSPLIT(A1,"+")," ")),10),LEFT(TOCOL(TEXTBEFORE(TEXTSPLIT(A1,"+")," ")),9))

wp231957

=HSTACK(SEQUENCE(COUNTA(TEXTSPLIT(A1,"+"))),RIGHT(TOCOL(TEXTBEFORE(TEXTSPLIT(A1,"+")," ")),10),LEFT(TOCOL(TEXTBEFORE(TEXTSPLIT(A1,"+")," ")),9))
前面添加序数

wp231957

公式还可以精简一点点
=LET(a,TEXTSPLIT(A1,"+"),b,TOCOL(TEXTBEFORE(a," ")),HSTACK(SEQUENCE(COUNTA(a)),RIGHT(b,10),LEFT(b,9)))

cexo790527

wp231957 发表于 2024-9-21 09:57
=HSTACK(SEQUENCE(COUNTA(TEXTSPLIT(A1,"+"))),RIGHT(TOCOL(TEXTBEFORE(TEXTSPLIT(A1,"+")," ")),10),LEFT( ...

感谢您的指导，我用EXCEL 2019试了一下。这个版本的没有HSTACK这个函数。我的WPS也是2019版的，同样查函数，里面也没有这个函数。受限于现有的电脑操作系统，我目前只能装2019版的EXCEL。有没有能够在2019版内解决的呢，恳请指导，谢谢！

祝洪忠-

两次分列

cexo790527

感谢两位大佬的细心指教，受益良多，感谢感谢!

沈默00

=LET(t,TAKE(TEXTSPLIT(A1," ","+"),,1),HSTACK(LEFT(t,LEN(t)-10),RIGHT(t,10)))

沈默00

cexo790527 发表于 2024-9-21 10:15
感谢您的指导，我用EXCEL 2019试了一下。这个版本的没有HSTACK这个函数。我的WPS也是2019版的，同样查函 ...

我也用WPS呢，你的WPS没升级吧，
到官网下载最新版就可以了，，WPS可以用大部分365函数的

佛山小老鼠

1. Option Explicit
   
2. Sub test()
   
3.   Dim Reg, Mat, Ma, arr1(1 To 10000, 1 To 2), k
   
4.   Set Reg = CreateObject("VbScript.RegExp")
   
5.   With Reg
   
6.     .Global = True
   
7.     .Pattern = "(￥\d,\d+\.\d+)(\d{4}-\d+-\d+ \d+:\d+)(?=还款)"
   
8.   End With
   
9.   Set Mat = Reg.Execute([A1] & "还款")
   
10.   For Each Ma In Mat
    
11.       k = k + 1
    
12.       arr1(k, 1) = Ma.submatches(0)
    
13.       arr1(k, 2) = Ma.submatches(1)
    
14.   Next Ma
    
15.     [A3:B1000].ClearContents
    
16.     [A3] = "还款金额": [B3] = "还款日期"
    
17.     [A4].Resize(k, 2) = arr1
    
18. End Sub
    

复制代码