×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

TEXTSPLIT in Dynamic Table - Excel

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

RE: TEXTSPLIT in Dynamic Table - Excel

Text to Columns comes to mind...



---- Andy

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

RE: TEXTSPLIT in Dynamic Table - Excel

(OP)
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

RE: TEXTSPLIT in Dynamic Table - Excel

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

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 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 a sample workbook that demonstrates how an event driven approach works.

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 Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close