Array and regex in Excel

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Alina
Newbie
Posts: 17
Joined: Sun Jul 04, 2021 9:02 am

Array and regex in Excel

Post by Alina » Sat Jul 10, 2021 10:42 pm

Good day!

Code: Select all

GetClipBoard>List,0
Separate>List,CRLF,Array1
XLOpen>C:\Users\I\Docs\doc.xlsm,1,xlBook
Let>k=0
Repeat>k
Let>k=k+1
Let>A1=Array1_%k%
XLSetCell>xlBook,Sheet,%k%,1,%A1%,scResult
Until>k=Array1_count
I run this code and I get list in column A:
text A 1 000,22 text B
text A 5 000 text B
...

How can I get list of numbers from column A to column B?
I've written this regex:

Code: Select all

Let>pattern=((?<=text A ).*(?= text B))
RegEx>pattern,text,0,matches1,num,0
MessageModal>matches1_1
But it works only for one value.
How can I get all numbers from column A? How to change my regex? How to join first macros with second macros (regex)? How to join regex and array together?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Array and regex in Excel

Post by Dorian (MJT support) » Mon Jul 12, 2021 10:16 am

I just tested this and it does what I think you want. Writing the numbers into column B.

Code: Select all

Let>pattern=((?<=text A ).*(?= text B))
GetClipBoard>List,0
Separate>List,CRLF,Array1

XLOpen>C:\Users\I\Docs\doc.xlsm,1,xlBook

Let>k=0
Repeat>k
  Let>k=k+1
  Let>A1=Array1_%k%
  RegEx>pattern,A1,0,matches1,num,0
  //MessageModal>matches1_1
  XLSetCell>xlBook,Sheet,%k%,2,%matches1_1%,scResult
Until>k=Array1_count
Yes, we have a Custom Scripting Service. Message me or go here

Alina
Newbie
Posts: 17
Joined: Sun Jul 04, 2021 9:02 am

Re: Array and regex in Excel

Post by Alina » Mon Jul 12, 2021 5:12 pm

Thank you very much, Dorian! It works perfectly! :D
I've only changed %A1% on %matches1_1% in XLSetCell.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Array and regex in Excel

Post by Dorian (MJT support) » Mon Jul 12, 2021 6:03 pm

Good catch. Edited.
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts