Split Text with Varying Delimiter

**richardking** · 08-06-2018, 04:42 AM

Hi Guys,

I need help with splitting text that has multiple delimiters.

Name	1	2	3	4	5	6	7	8	9
Jim (1) Brown (2) Matt (3)	Jim	1	Brown	2	Matt	3

The table shown has 10 columns and 2 rows and originates at cell A1.
The entry in cell A2 is manually added and would continue down depending on the X number of entries.
The expected results are as shown on the table.

Cell B2 is where I would put my first formula, to be dragged down and to the side.
I used this formula:
=TRIM(MID(SUBSTITUTE($A2,"(1)",REPT(" ",LEN($A2))),(B1-1)*LEN($A2)+1,LEN($A2)))

It is with the part that is colored red that I am having a problem. It only works if it can find "(1)", and if the value inside the parenthesis changes, it no longer works.
I dont know how to incorporate wildcards. I tried, but it did not work.
Hoping you can help me out.
Thanks as always.

**AliGW** · 08-06-2018, 04:45 AM

As always, will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**richardking** · 08-06-2018, 04:54 AM

Hi AliGW,

Apologies for that. Attached is the workbook.

**AliGW** · 08-06-2018, 04:58 AM

Try this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"(","|"),")","|"),"|",REPT(" ",LEN($A2))),(B1-1)*LEN($A2)+1,LEN($A2)))

**AliGW** · 08-06-2018, 05:02 AM

And to get rid of your helper row:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"(","|"),")","|"),"|",REPT(" ",LEN($A2))),(COLUMNS($A:A)-1)*LEN($A2)+1,LEN($A2)))

**AliGW** · 08-06-2018, 05:09 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**richardking** · 08-06-2018, 05:13 AM

Wow! Absolutely wonderful. Worked like a charm.
Thank you so much. I didn't know you can figure it out that fast.
Pardon my ignorance, why is there a need for "|"?

**AliGW** · 08-06-2018, 05:20 AM

It could be any delimiter of your choosing. Basically, your formula requires the delimiters to be the same, so you could just as easily do this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"(",")"),")",REPT(" ",LEN($A2))),(COLUMNS($A:A)-1)*LEN($A2)+1,LEN($A2)))

I just like the pipe symbol.

**richardking** · 08-06-2018, 05:23 AM

Wow thank you.
This is one of the fastest solutions I have gotten so far.

**AliGW** · 08-06-2018, 05:25 AM

You're welcome!

You got lucky - I happened to be here and I happened to know the answer.

**richardking** · 08-06-2018, 05:37 AM

True to that!

**Ghozi Alkatiri** · 08-06-2018, 06:20 AM

try this start at B2
=IF(ISEVEN(COLUMN(A:A)),FLOOR(COLUMN(A:A)/2,1),SUBSTITUTE(MID(SUBSTITUTE($A3,")",REPT(" ",LEN($A3))),TRUNC((COLUMN(A:A)-1)/2)*LEN($A3)+1,LEN($A3)),"("&TRUNC((COLUMN(A:A)-1)/2)+1,""))

copy right

**AliGW** · 08-06-2018, 06:23 AM

Why so complicated a formula? In what way does it improve on the suggestions made earlier in the thread? I'm all for alternative options, but this one is far more complicated than it needs to be, in my view.

**Ghozi Alkatiri** · 08-06-2018, 06:24 AM

try this start at B2
=IF(ISEVEN(COLUMN(A:A)),FLOOR(COLUMN(A:A)/2,1),SUBSTITUTE(MID(SUBSTITUTE($A3,")",REPT(" ",LEN($A3))),TRUNC((COLUMN(A:A)-1)/2)*LEN($A3)+1,LEN($A3)),"("&TRUNC((COLUMN(A:A)-1)/2)+1,""))

copy right

**AliGW** · 08-06-2018, 06:27 AM

But WHY? What is the benefit of such a complicated formula over the much simpler one I offered? Please answer the question.

**jtakw** · 08-06-2018, 09:55 PM

Hi,

How about this one, in B2 copied across.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"(",""),")","")," ",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))

Split Text with Varying Delimiter

LinkBack

Thread Tools

Rate This Thread

Display

Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Re: Split Text with Varying Delimiter

Thread Information

Users Browsing this Thread

Similar Threads

Separating alpha and numeric characters, no delimiter, varying lengths

How to split a column at first delimiter only

[SOLVED] Separating alpha and numeric characters, no delimiter, varying lengths

[SOLVED] Split text and number field with no delimiter

Split Text using numbers as Delimiter.

Split 1 cell into 2 using delimiter by function not text to columns function

Split Cell by Delimiter, Move to New Row...

Bookmarks

Bookmarks

Posting Permissions