extracting a specific value from a string

Originally Posted by ChemistB

Whether it's done with Formulas or VBA (both are possible), we'd need specific information to tell Excel what specifications you are using to extract? Are there really underscores between each grouping or are there spaces? Are these two examples covering the range of variability one might see in your text?

Yes, there are underscores in the string and the previous examples cover the variation of each string. I'm trying to extract the "123C12345678" portion of the string (shown at the previous examples where the numbers are random). . I've tried editing Domski's macro for extracting numbers from a string but the result i get contains all the numbers, underscores & the letter "C" in the string.

examples:

John_C_Doe_12345678_123C12345678_abcd = _C__12345678_123C12345678_

Jack_Bauer_12345678_123C12345678_abcd = __12345678_123C12345678_
the

the code that I'm using now goes like this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Dim rngLoopRange As Range, i As Long, strResult As String, lngCharacter As Long

With Selection
For Each rngLoopRange In Selection
strResult = ""
For i = 1 To Len(rngLoopRange)
lngCharacter = Asc(Mid(rngLoopRange, i, 1))
If (lngCharacter >= 48 And lngCharacter <= 57) Or (lngCharacter = 67 Or lngCharacter = 95) Then
strResult = strResult & Mid(rngLoopRange, i, 1)
End If


Next i
rngLoopRange = strResult

Next rngLoopRange
End With

End Sub

Help :confused

PS. shoutout to Domski for havin a great macro

Please take a moment to read the rules and then apply code tags around your code in accordance with rule #3. Thanks.

Originally Posted by darkyam

Please take a moment to read the rules and then apply code tags around your code in accordance with rule #3. Thanks.

My bad. Here's the code

Please Login or Register  to view this content.

Thanks to everyone for replying

Here's some sample data that i'm working with. Thanks in advance!

Thanks for the response. I inserted the custom code function you showed but when i call the function, it always give me the result "No Term". Any other hints?

Originally Posted by DonkeyOte

The UDF was based on your sample file - see attached (all results are per your expected values)

I was able to make it work after re-downloading the worksheet. Thanks so much for all the help

just a follow up. Could someone help me tweak this VBA code so it could give the result i'm looking for? I really want to make it work. It if isn't possible then it's no biggie. Thanks again!

Please Login or Register  to view this content.

Originally Posted by DonkeyOte

That's a pretty vague post... the "result" you're looking for is what exactly ?

As discussed previously the UDF generated the exact same values as your "expected" results so if they're no longer the desired results it might be an idea to elaborate regards what is.

Let me attach a worksheet that uses the macro, that I tried tweaking, to explain it better. Your UDF code really is a big help however, the "result" that I need will be sorted and the UDF won't let you sort the "result" since the "result" will be in formula format: "=stringextract(cellnumber)".

=SUBSTITUTE(MID(MID(C2,FIND("20",C2),99),FIND("_",MID(C2,FIND("20",C2),99))+1,99),"_"&TRIM(RIGHT(SUBSTITUTE(MID(MID(C2,FIND("20",C2),99),FIND("_",MID(C2,FIND("20",C2),99))+1,99),"_",REPT(" ",99)),99)),"")

copy down as far as needed