Hey @CMSGuy
Took a bit of experimenting to get it right, but we can essentially double split it and remove blanks, then for each split-split we trim out non-valid characters like line feed and carriage return using Regex matching, and then for the Qual we keep all the Other special characters
Code for that button (which just sets a variable - that I then use as the Items for that gallery):
Set(gblSplitTable,
ForAll(
RemoveIf(Split(Trim(TextToCheck.Text),";"), Value="") As OuterColumns,
With(
{
InnerColumns: Split(OuterColumns.Value, "###")
},
{
Name: Concat(Split(Last(FirstN(InnerColumns.Value, 1)).Value, ""),
If(IsMatch(Value, "[a-zA-Z0-9 ]"), Value)),
QualID: Concat(Split(Last(FirstN(InnerColumns.Value, 2)).Value, ""),
If(IsMatch(Value, "[0-9 ]"), Value)),
Qual: Concat(Split(Last(FirstN(InnerColumns.Value, 3)).Value, ""),
If(IsMatch(Value, "[ a-zA-Z$&+,:;=?@#|'<>.^*()%!/-]"),Value)),
Date: If(Len(Concat(Split(Last(FirstN(InnerColumns.Value, 4)).Value, ""),
If(IsMatch(Value, "[0-9 ]"),Value)))>0,
// Dates as Numbers in Excel use 1899-12-30 as initial to add to
// (so subtract 2 from 1900-01-01 [earliest PowerApps Date function value])
Text(DateAdd(
Date(1900, 1, 1),
Value(Concat(Split(Last(FirstN(InnerColumns.Value, 4)).Value, ""), If(IsMatch(Value, "[0-9]"),Value))) - 2,
TimeUnit.Days),
"[$-en-US]dd/mm/yy"
),
Blank())
}
)
)
)
Have also attached a proof of concept app where you can play around with what I've created and transfer what you need to your code 🙂
Cheers,
Sancho
