TEXTSPLIT in Dynamic Table - Excel
TEXTSPLIT in Dynamic Table - Excel
(OP)
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
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
Talk To Other Members
RE: TEXTSPLIT in Dynamic Table - Excel
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: TEXTSPLIT in Dynamic Table - Excel
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
RE: TEXTSPLIT in Dynamic Table - Excel
Skip,
Just traded in my OLD subtlety...
for a NUance!
"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!
RE: TEXTSPLIT in Dynamic Table - Excel
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,
for a NUance!
"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!
RE: TEXTSPLIT in Dynamic Table - 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 List.Count(Text.Split(String,[input]))-1 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
RE: TEXTSPLIT in Dynamic Table - Excel
Here's the Event code...
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 SubSkip,
Just traded in my OLD subtlety...
for a NUance!
"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!