Editing and taking a part of a string

[eawachte]

I have a string it is LF_LCA_079_010813_Curved_1_4_Short it reads into vba from a file and is give the variable name LFDesc and dim LFDesc as string

Now i would like to pull LCA_079 from that string and create a new string with this text. I can do it in excel using the MID and Find functions, but i really want to do it in vba to clean the sheet up.

I'm having a hard time figuring out the instr function for vba could you help me with the code PLEASE!!

Thank you in advance

Gene

 

[par60056]

newStr = mid(LFDesc,instr(1,LFDesc,"LCA",vbTextCompare),7)

 

[eawachte]

newStr = mid(LFDesc,instr(1,LFDesc,"LCA",vbTextCompare),7)

Thank you for the response!

I apologize i was thinking through my question while i grabbed lunch and remembered I had one more condition i needed to add to my post.

With LF_LCA_079_010813_Curved_1_4_Short I still want to start where you started with the last post and get "LCA", but this could be any length, how would I fine the (2nd) "_" after 079.

I assuming the "7" in your code is the amount of characters?

Thank you!

 

[par60056]

yes the 7 is the number of characters to extract.

Finding the second "_" can be a bit tricky and a long formula

I hope somebody can suggest something better than this:

Function getSecondUnder(sourceString) As String
Dim stStart As Integer
Dim stEnd As Integer
Dim newStr As String


stStart = 1
stEnd = Len(sourceString)
If (InStr(1, sourceString, "LCA", vbTextCompare)) Then
    stStart = InStr(1, sourceString, "LCA", vbTextCompare)
    stEnd = InStr(stStart + 4, sourceString, "_")
    stEnd = InStr(stEnd + 1, sourceString, "_")
    stEnd = stEnd - stStart
End If
    
getSecondUnder = Mid(sourceString, stStart, stEnd)
End Function

 

[Rick Rothstein]

I have a string it is LF_LCA_079_010813_Curved_1_4_Short it reads into vba from a file and is give the variable name LFDesc and dim LFDesc as string

Now i would like to pull LCA_079 from that string and create a new string with this text. I can do it in excel using the MID and Find functions, but i really want to do it in vba to clean the sheet up.

I'm having a hard time figuring out the instr function for vba could you help me with the code PLEASE!!

Does this code do what you want...

Function Get2ndAnd3rd(SourceString As String) As String
  Dim Parts() As String
  Parts = Split(SourceString, "_")
  Get2ndAnd3rd = Parts(1) & "_" & Parts(2)
End Function

 

[par60056]

ok looking at my own code I see it can be done in 1 long formula with a few substitutions:

newStr= Mid(LFDesc, InStr(1, LFDesc, "LCA", vbTextCompare), InStr(InStr(InStr(1, LFDesc, "LCA", vbTextCompare) + 4, LFDesc, "_") + 1, LFDesc, "_") - InStr(1, LFDesc, "LCA", vbTextCompare))

but it isn't pretty.

 

[par60056]

I thought about splitting but I was not sure if the LCA would always be the beginning of a part.

I think they would want parts 1-4