TEXTSPLIT in Dynamic Table - Excel

[remeng]

Hello,

I am trying to perform a TEXTSPLIT in a dynamic table. I know that this isn't supported, but I am looking for a workaround. I need the data to be divided by the delimiter.

Example:

Input:
bob.frank.ken.paul

Column A: bob
Column B: frank
Column C: ken
Column D: paul

The string can be up to 15 sections (15 columns needed).

I have tried most of the common functions, but I either get everything after the N occurrences, but not the value between the delimiter.

I'd like to avoid macros as much as possible due to who will be using the form.

Thanks

Mike

 

[Andrzejek]

[tt]Text to Columns[/tt] comes to mind...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Hi Andy,

I need to use a dynamic table and text to column won't work in this case. It needs to be formula driven.

Thanks for the idea though.

Mike

 

[SkipVought]

How about a "macro" that works just like a spreadsheet formula?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

The TEXTSPLIT() function is available in Excel for Microsoft 365.

EDIT
The TEXTSPLIT() function does not work in a Structured Table (dynamic table)

Rather you would need an Event driven macro, which requires no interaction with the user.

Whenever a Worksheet Change occurs in the target sheet, a macro would run that looks to determine if the change was in the column containing the target string to be parsed. It would then place the results beginning in the specified column for the target row.

Maybe you need to explain in detail how you envision the process of adding incremental rows of data.
For instance, where does the input come from if the results start in column A?
What do you mean by a dynamic table?

I'll assume that column A is the input column.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

If you like Power Query in excel:
Case 1 - fixed number of columns:
In Power Query environment direct command in UI, example here
Case 2 - unknown number of output columns:
First, create helper query to determine number of columns. Add column with [tt]List.Count(Text.Split(String,[input]))-1[/tt] to calculate number of columns for each row. Calculate maximum. Create a dynamic list of required headers.
Next in main query repeat action from case 1, replace hard-coded recorded list of headers by dynamic list.


combo

 

[SkipVought]

Here's a sample workbook that demonstrates how an event driven approach works.

Here's the Event code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'SkipVought 2023 Aug 7
'Parses strings, using PERIOD (.) delimiter, that are entered in Column A
'and places the results in Column A and following
    Dim a As Variant, i As Integer
   
    With Target
        If .Cells.Count = 1 Then
            If Not Intersect(.Cells, Columns(1)) Is Nothing Then
                a = Split(.Value, ".")
                For i = 0 To UBound(a)
                   Cells(.Row, .Column + i).Value = a(i)
                Next
            End If
         End If
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!