CODEWITHSUNDEEP
excelvba
anky
anky

Reputation: 75080

Split a string but consider 1 separator in all splits

I have an output_file which looks like 0001_1234_abcd_defg_U_2018.08.24-14.50.23.TIF

I am trying to split this file value and paste the splitted values into a table in column C:H starting the immediate available row.

I am using the below code to achieve this.

 tbl.Range(LastRow, "C").Offset(1).Value = Split(output_file, "_")(0)
 tbl.Range(LastRow, "D").Offset(1).Value = Split(output_file, "_")(1)
 tbl.Range(LastRow, "E").Offset(1).Value = Split(output_file, "_")(2)
 tbl.Range(LastRow, "F").Offset(1).Value = Split(output_file, "_")(3)
 tbl.Range(LastRow, "G").Offset(1).Value = Split(output_file, "_")(4)
 tbl.Range(LastRow, "H").Offset(1).Value = Split(output_file, "_")(5)

But sometimes my output_file has 2 underscores in a feild eg 0001_1234__abcd_defg_U_2018.08.24-14.50.23.TIF. The above code fails in such cases. How to I handle this scenario.

Thanks in advance. :)

Upvotes: 2

Views: 88

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

I hope this helps

Sub test()

Dim a() As String

a = Split(replace("123_456_789","__","_"), "_")

Range("h1").Resize(1, UBound(a) + 1).Value = a

End Sub

Upvotes: 3

user4039065
user4039065

Reputation:

Try,

tbl.Range(LastRow, "C").resize(1, 6).Offset(1, 0) = _
  split(replace(output_file, "__", "_"), "_")

Upvotes: 3

Related Questions