Read specific text and write into next column

[DTECH (Frank DTech)]

Hi,

As title above, I was thinking of eliminate some text inside cell and output it into next column. I know some will say using regex but I don’t familiar with the argument to code it. Appreciate the solutions

[avejr748 (Ave Salvante Jr)]

If the data in column a will always be formatted like that, a simple text split will do:

Assign:
strTextfromColumnA = row(“A”).ToString
Assign text to column B:
row(B) = strTextfromColumnA.Split(" “c,0) & " " & strTextfromColumnA.Split(” "c,2)

[jack.chan]

for regex solution, use this
TEST.xaml (9.9 KB)
test.xlsx (8.3 KB)

steps

1. read excel to datatable
2. add new column to dt ("Result")
2. foreach row in excel:
a) assign dateVal = system.Text.RegularExpressions.Regex.Match(currentRow(0).ToString, "([\d]{2}\/[\d]{2}\/[\d]{4})").Groups(1).Value
b) assign amountVal = system.Text.RegularExpressions.Regex.Match(currentRow(0).ToString, "([\d]+\.[\d]+)").Groups(1).Value
c) assign currentRow("Result") = dateVal + " " + amountVal

before

after

[DTECH (Frank DTech)]

Thanks appreciate the dynamic solution. Cheers