Need Help to concatenate all columns in certain row from one Datatable

Dear Sir/Madam

The datatable writen in excel is generated from a string.
Thus the numbers of columns in a row is not fixed.
In my case, I would like to get all column items for row 3, which is highlighted in yellow , and joint them in the format of

“9023459211 Oct 01, 2020 39267.19 GBP 0 0 39267.19”

and assign this value to a Variable.

would you please guide me how to count the number of columns that is not blank in a row. and how to joint all the columns with space?

Much appreciation in advance!

B/R Diana

Column1 Column2 Column3 Column4 Column5 Column6 Column7
“DOCUMENT_REFERENCE_NUMBER DOCUMENT_DATE” DOCUMENT_AMOUNT DOCUMENT_CURRENCY AMOUNT_WITHHELD DISCOUNT_TAKEN AMOUNT_PAID
“9023459211 Oct 01, 2020 39267.19 GBP 0” 0 39267.19
example.xlsx (10.0 KB)

I have two solutons:

1.you can use Split(dt.rows(1)(0).tostring,yourBlank)
2. Use text to columns in VBA
Forum0611.zip (12.1 KB)

Sub Main()
Sheets(“Sheet1”).Select
Range(“A3”).Select
Selection.TextToColumns Destination:=Range(“A4”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True
End Sub