-3

Let me put the question in a precise way How to split based on pattern in vba

<cr>1st data<rc>2nd data<ab>3rd data<ba>4th data

i use the following way to split it in perl

my @values = split(/<([\w]+)>/,$string);

and it gives me output as

cr 1st data rc 2nd data ab 3rd data ba 4th data

i want to do the same with vba split function what should be my approach

Community
  • 1
  • 1
Rajat Batra
  • 3
  • 4
  • Take a look at the `Split` function. – Comintern Jul 22 '16 at 18:47
  • Hi sorry for the problem The string will be like `first data1second data2third data3Fourth data4` – Rajat Batra Jul 22 '16 at 18:47
  • hi i looked at it but i was not able to understand anything properly since i donot know vb at all i have already done this with perl script but i have no idea how to do with vba so if you can provide an example it will be really helpful – Rajat Batra Jul 22 '16 at 18:49
  • If you can give me a sample example for split function this will help a lot – Rajat Batra Jul 22 '16 at 18:51
  • It's exactly the same as [in Perl](http://perldoc.perl.org/functions/split.html) (2nd overload). – Comintern Jul 22 '16 at 18:54
  • Stack Overflow is not a "code for me" or a "teach me to code" site. If you have code that is not working please put it in the original post using edit, with specific direction as to the problem with the code and we will help overcome that specific problem. – Scott Craner Jul 22 '16 at 19:09
  • @ScottCraner i understand the issue but all i want is just an example however i have tried some thing which i will post – Rajat Batra Jul 22 '16 at 19:40
  • @ScottCraner please try to understand i donot want to learn VB if you can give me an example or redirect me where this is explained that will be helpful – Rajat Batra Jul 22 '16 at 19:50
  • @ScottCraner i already know the split function i want to know how to add regex to it this thing is pretty easy in perl but i am struggling in VBa – Rajat Batra Jul 22 '16 at 20:02
  • Copying and posting code from http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops is not the same as posting your code. What have you tried to modify the code to do what you want? It is expected that the poster will do some homework on their own. This is not the site for what you want. We are not here to do your work for you. – Scott Craner Jul 22 '16 at 20:07
  • hi why you guys are not understanding i have no issue with split function i want to know how to put regex into it so that it splits based on pattern – Rajat Batra Jul 22 '16 at 20:19
  • @RajatBatra - Because I'd argue that `Split` is the better solution to your problem than using a regular expression. – Comintern Jul 22 '16 at 22:23

1 Answers1

0

Example code for Split:

Sub splitandUbound()


Dim s() As String, s1 As String
Dim i As Integer

s1 = "C:\_KTemp\TEMP\Test Macro for FolderExists\test\test1\test2\test3"

' Create the array
s = Split(s1, "\")

' show results of the array
For i = 0 To UBound(s)
    debug.print s(i)
Next

End Sub
Kerry White
  • 416
  • 2
  • 10
  • thanks @kerry the problem is my data is some what like `first data1second data2third data3Fourth data4` and i want to split based on tag and want out put as first data 1 second data2 third data3 fourth data 4 for which i need to put regex in split and i am struggling with that and need help with that in perl this is very straight forward i am not at all a vb expert or even beginner – Rajat Batra Jul 22 '16 at 20:17
  • Ah! It sounds like you may need a formula using "Mid" ... Example: I put your data in `A1` and in `B1` I put this formula: `=MID(A1,FIND("",A1,1)+4,FIND("",A1,1)-5)` This gave me what was between `` and ``. You'd just need to do the next part of the formula in `C1` something like: `=MID(A1,FIND("",A1,1)+4,FIND("",A1,1)-5)` and so on... – Kerry White Jul 22 '16 at 20:27
  • sorry i may sound a lot amateur but what are A1 B1 C1 can you explain a little – Rajat Batra Jul 22 '16 at 20:32
  • Oh i got it a1 b1 c1 are row columns of excel but when i try the formula suggested by you i get #value error – Rajat Batra Jul 22 '16 at 20:40
  • Thanks a lot kerry it worked you were really really helpful – Rajat Batra Jul 22 '16 at 20:50
  • I appreciate a lot what you all guys do for everyone – Rajat Batra Jul 22 '16 at 20:50