trim characters before and after "_"

[ymeyaw]

Dear experts,

I had excel column with following format :

123_ABC_DATA1_1
234_BCD_DATA2_44

and i wish to trim the data so that i will have:
DATA1
DATA2

and i do not want to use import files with "_" . How should i do that? This is the last part of my data processing using VBA and i really hope some one can help me on this. Thanks for your help...

 

[GTO]

Are we safe in presuming that there are consistently two underscores fore and one aft the part we want left?

 

[Swaminathan]

Sub Macro1()
'
' This will just delimit based on the char "_"
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2)), _
TrailingMinusNumbers:=True
End Sub

 

[ymeyaw]

Are we safe in presuming that there are consistently two underscores fore and one aft the part we want left?

Hi GTO,

Yes, There will be always a 2 underscore before and one underscore after which i only need the "DATA" part only.

 

[GTO]

I imagine you already have your range referenced, so just a demo to try.

Sub exa()
Dim REX As Object
Dim cell As Range
    
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .Pattern = "([^_]*_)([^_]*_)([^_]+)(_.*)"
    
        For Each cell In Range("A2:A5")
            If .Test(cell.Text) Then cell.Offset(, 2).Value = .Execute(cell.Text)(0).SubMatches(2)
        Next
    End With
End Sub

Before/After:

Excel Workbook
	A	B	C
1
2	123_ABC_DATA1_1		DATA1
3	234_BCD_DATA2_44		DATA2
4	12321_A_Data3_45		Data3
5	BASCD_ADS_Data4_0		Data4
Sheet1

Excel 2003
Hope that helps,

Mark

 

[ymeyaw]

Hi Mark,

Thanks for your help!!It definetely clear my pain. Thanks a million!!!!

 

[ymeyaw]

Hi Swaminathan,

Thanks . THis works too!!!! Thanks alot for your kindly help!!