Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[Rick Rothstein]

just one string of numbers per line either 6 or 9, never 4

You said originally 6 to 9 digits, so I went with that (I can change it if need be). I think a formula solution would be long, messy and full of repeats, so I am offering you a UDF (user defined function) for you to use instead...

Function Get6To9Digits(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, "|")
  For X = 0 To UBound(Parts)
    If Not Parts(X) Like "*[!0-9]*" And Len(Parts(X)) > 5 And Len(Parts(X)) < 10 Then
      Get6To9Digits = Parts(X)
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Get6To9Digits just like it was a built-in Excel function. For example,

=Get6To9Digits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[RGMaveric]

That works great Thank you,

To add on is there a way to edit to to pull only one of the garbled texts highlighted in blue

SP6BREG3NAKHAK|242925|SP6SREG3NAKHAK|1089
242925|SP6SREG3NAKHAK|1089
[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="width: 64"]SUPERSAC|300021973|SQUATTOMAN|9004[/TD]
[/TR]
</tbody>[/TABLE]


Also, is there a place i can go that I can learn how to do this fluently as the moderators of this forum have?

 

[Rick Rothstein]

To add on is there a way to edit to to pull only one of the garbled texts highlighted in blue

SP6BREG3NAKHAK|242925|SP6SREG3NAKHAK|1089
242925|SP6SREG3NAKHAK|1089
[TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="width: 64"]SUPERSAC|300021973|SQUATTOMAN|9004
[/TD]
[/TR]
</tbody>[/TABLE]

What would be the "garbled" text in the third one... SUPERSAC or SQUATTOMAN? Why?

 

[RGMaveric]

The 3rd one is a bad example. The characters highlighted in blue represent skus in an excel file that I need to reference to import them to a system. The numbers and text strings are separate fields. However on the excel file are combined into one unorganized mess or things I keep trying to figure out how to put into separate cells. This is a daily occurance

 

[Rick Rothstein]

The 3rd one is a bad example. The characters highlighted in blue represent skus in an excel file that I need to reference to import them to a system. The numbers and text strings are separate fields. However on the excel file are combined into one unorganized mess or things I keep trying to figure out how to put into separate cells. This is a daily occurance

Are you saying your third example will never happen? If it could, tell me how you distinguish between your SKU and any other non-SKU text so I make the code recognize SKUs from non-SKUs.

 

[RGMaveric]

The 3rd example won't happen in the future, so if the code returns the 3rd example as a string, I'll be able to fix that. The skus are various lengths containing letters and numbers. Making this incredibly difficult and that's why I don't mind if the 3rd example is returned.

The only saving grace is that, the example in the first line, and the example in the third line will never be in the same line.

 

[Rick Rothstein]

The 3rd example won't happen in the future, so if the code returns the 3rd example as a string, I'll be able to fix that. The skus are various lengths containing letters and numbers. Making this incredibly difficult and that's why I don't mind if the 3rd example is returned.

The only saving grace is that, the example in the first line, and the example in the third line will never be in the same line.

Okay then, give this UDF a try...

Function GarbledText(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, "|")
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*[!0-9]*" Then
      GarbledText = Parts(X)
      Exit For
    End If
  Next
End Function

 

[RGMaveric]

That works great. Thank you tons sir.

Is there a starting point as at where I can learn how to do this without assistance?

 

[Rick Rothstein]

Is there a starting point as at where I can learn how to do this without assistance?

I have been programming, nearly continuously, since 1981 and I am completely self-taught, so everything I do programming-wise comes pretty automatic anymore. In addition, I seem to approach coding solutions differently then my fellow volunteers do. Hence, I am not really the person to ask that question of. I do know that there are a ton of tutorials online that you can Google for, but your learning style (preferences) will determine whether you find one type of tutorial more suitable than another.

 

[Peter_SSs]

Rick has given you some good UDFs that certainly seem to do what you asked.

If it happens that the number & text you are looking for are always the first two parts of your string (just the order is unknown) as is the case with the examples provided, then standard functions may do the task.
Here is one way.

Excel Workbook
	A	B	C
1	Data	Number	Text
2	SP6BREG3NAKHAK|242925|SP6SREG3NAKHAK|1089	242925	SP6BREG3NAKHAK
3	242925|SP6SREG3NAKHAK|1089	242925	SP6SREG3NAKHAK
4	SUPERSAC|300021973|SQUATTOMAN|9004	300021973	SUPERSAC
Extract from Text

Is there a starting point as at where I can learn how to do this without assistance?

You are looking at it.
I have learnt the majority of what I know from this forum. It is certainly where I got started.