Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TEXTSPLIT in Dynamic Table - Excel

Status
Not open for further replies.
remeng

remeng

Technical User
Jul 27, 2006
523
US
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
 
Sort by date Sort by votes
[tt]Text to Columns[/tt] comes to mind...

ToC_svwlbl.png


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
How about a "macro" that works just like a spreadsheet formula?

Skip,

[glasses]Just 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!
 
Upvote 0 Downvote
9399F5FF-FCA1-4A57-A295-A69D03252ACC_rnwvvd.jpg


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,

[glasses]Just 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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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,

[glasses]Just 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!
 
 https://files.engineering.com/getfile.aspx?folder=3a78ce3c-938d-4fb9-85a7-9cb556e60e99&file=Variable_Parse.xlsm
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Excel Pivot Table - Combine Values into a single row
Replies
16
Views
2K
remeng
remeng
Swi
  • Locked
  • Question
Excel Webservice
Replies
2
Views
529
mintjulep
M
Kennelbloke
  • Locked
  • Solved
Excel Unique-Filter issue
Replies
3
Views
471
SkipVought
S
bkoopers
  • Locked
  • Question
Problem with Excel pasting into filtered cells
Replies
1
Views
322
combo
combo
BugZap13
  • Locked
  • Question
Excel formula to count occurrences of a value in a different sheet
Replies
5
Views
900
bs6600
bs6600

Part and Inventory Search

Sponsor

Back
Top