2

I wanted to know if there is a way I could extract a sub string's out a string by specifying the beginning few characters and the end character.

As an example I have string like at the bottom of the question in a cell in my workbook and every cell has a similar big string. I would like to extract all the name's into an array.

The "c1-mc-" will always be a prefix for a name. I was hoping I could use a function in which I could specify that for each substring starting with "c1-mc" and ending with vbLf(enter), extract those. I think Instr() and Split() could help but not sure how to proceed.

"Str: 1/2/1
End  : 1/2/2
Name: cl-mc-23223322
Name: c1-mc-dddssda
Info: alot of detail
Name: c1-asa-dddssda
task: asdf
Name: c1-mc-xd132eds"



<the code which works>    
For Each rng1 In table1.DataBodyRange.Columns(8).Cells

MyString = rng1.Value
Do Until InStr(MyString, "c1-mc") = 0      
namestart = InStr(MyString, "c1-mc")
name = Mid(MyString, namestart)
nameend = InStr(name, vbLf) - 1
name = Left(name, nameend) 'this gives you a name
namestart = InStr(name, "c1-mc")
name = Mid(name, namestart)
nameend = InStr(name, " ") - 1
If (nameend = -1) Then
nameend = Len(name)
End If
name = Left(name, nameend) ' gives you name incase there are no next lines
MyString = Replace(MyString, name, "") 'this cuts the original string so it now starts where the name ended.
MsgBox name
i = i + 1
Loop
Next
0m3r
  • 12,286
  • 15
  • 35
  • 71
  • 1
    If you are doing a lot of this, you may find looking into regular expressions in VBA of use. It allows for all sorts of slicing and dicing of string based on criteria. This is [an excellent post](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) on the topic. – Byron Wall Jun 19 '15 at 16:48
  • The string you are showing me is this: "Str: 1/2/1End : 1/2/2Name: cl-mc-23223322Name: c1-mc-dddssdaInfo: alot of detailName: c1-asa-dddssdatask: asdfName: c1-mc-xd132eds" There are no "enters" in a string. Maybe you mean "Str: 1/2/1" & vbLF & "End : 1/2/2" & vbLF & "Name: cl-mc-23223322" & vbLF... – David G Jun 19 '15 at 16:50
  • @Byron thanks for the reference. – Gary's Student Jun 19 '15 at 16:55

1 Answers1

0

EDIT after re-reading your question I think I didn't answer it correctly. Please give more details as to what is actually contained in each cell, and how many cells we are talking about (1?).

A string is a concatenation of characters. Writing your string on several lines doesn't mean it actually changes. The line change happens when you enter chr(10) or vbLF, as you said. I'm not sure which part of the string you posted you wanted to extract. Assuming you want to take the name of a cell, and that the string is held in the string variable [mystring]:

Dim name as string
Dim namestart as integer
Dim nameend as integer

namestart = Instr(Mystring,  "c1-mc-" )
name  = Mid(Mystring, namestart + 1)
nameend = Instr(Mystring, vbLF)
name = Left(name, nameend)

Now name would contain the name of your string. Test it out (I haven't, you might have to adjust a few minor things) and when you have it, use a for loop to loop through your cells and add the name to your desired array.

EDIT 2: Since you want to extract all instances of the name in your cell, I would change it to this:

Dim name as string
Dim namestart as integer
Dim nameend as integer
Dim namearray() as string
Dim i as integer

Do Until Instr(Mystring,  "c1-mc-" ) = 0 'will continue filling the array until Mystrign no longer has any names)
    namestart = Instr(Mystring,  "c1-mc-" )
    name  = Mid(Mystring, namestart + 1)
    nameend = Instr(Mystring, vbLF)
    name = Left(name, nameend) 'this gives you a name
    Mystring = Mid(Mystring, Instr(Mystring, name) ) 'this cuts the original string so it now starts where the name ended.
    namearray(i) = name
    i = i + 1
Loop
David G
  • 2,315
  • 1
  • 24
  • 39
  • The string I posted in my question is a cell value of a column which describes an event. There can be around 4000-5000 rows with the same field. In my question , you can see that there are 4 instances of name I.E "c1-mc-23223322" and so on. I want all the names extracted in an array. The code you posted above, how can I use it to run more than once on the cell value? –  Jun 19 '15 at 16:58
  • @Byron thank you, i'll look into regular expressions as well –  Jun 19 '15 at 17:00
  • Did my edit help you a bit? Now it should take all the names in the cell (I still haven't tested it, so there is a chance thre is a +1 to be added to the character positions, for example. Use debug.print to figure out what is added and what is cut.) – David G Jun 19 '15 at 17:08
  • I am working with the code you gave. There is a bit of a problem but I am able to extract the job name. The problem is that it is not resizing the string and I end up in a never end loop. I'll post the code soon so you can have a look. –  Jun 19 '15 at 17:34
  • I have posted the code. As I said earlier, I enter a never ending loop as the string is not being resized after removing the first instance. The string I am processing as of now has one name repeating itself 4 times. Could it be the reason? –  Jun 19 '15 at 17:42
  • I think it is simply not cutting it right. If you write debug.print(mystring) before the loop, what does it write in the execution window? Does the string actually shorten? – David G Jun 19 '15 at 18:26
  • Thanks alot for your help!!! I have managed to make it work finally. I just have had a situation where not every name has an "vblf(enter)" at the end. Some have a space as well so I had to modify the code a bit. Posting it soon on the forum at the top. –  Jun 19 '15 at 18:37
  • Let me know if you think I could handle it better for cells which have names with a space in between instead of an enter. I added that little if in between. But not sure if its the best way –  Jun 19 '15 at 18:44
  • Alright, nice! I don't see a way to chop it up more efficiently. Maybe there is one, but unles this is particularily slow to execute there is no need. Feel free to mark question as answered if you feel it's resolved! – David G Jun 19 '15 at 19:05
  • Thanks , it is resolved. Thank you for your time. I'm trying to figure how to mark it as answered, –  Jun 19 '15 at 21:32