Formula to split text based on a specific delimiter

**Roibeard** · 12-08-2020, 04:39 PM

Hi All,

I'm using the formula in the attached spreadsheet to split out values in a single into separate columns based on "," as a delimiter.

The formula works fine up to a point, but after a certain number of valves it produces an error across every column.

I'm thinking that one of the functions used might have an inbuilt character limit, where by if the single cell it's reading contains too much data, the formula fails to function.

The attached spreadsheet show the 2 scenarios, with the top section working as intended, but the bottom in error due to added values in cell B5.

I would very much appreciate if you guys could shed some light on this, or maybe provide an alternate means to accomplish the same result.

Thank you in advance,

Robert.

**6StringJazzer** · 12-08-2020, 05:05 PM

Here is a VBA function that will do it. VBA is more robust about string lengths.

Please Login or Register  to view this content.

**Limor_OP** · 12-08-2020, 05:12 PM

Try this in C5:
=TRIM(MID(SUBSTITUTE($B5,", ",REPT(" ",450)),(C$3-1)*450+1,450))

**Fluff13** · 12-08-2020, 05:21 PM

Another option in D5 copied across
=TRIM(MID(SUBSTITUTE(SUBSTITUTE($B5,", ",REPT(" ",LEN($B5)),C$3),",",REPT(" ",LEN($B5)),C$3),SUM(LEN($B5:B5)),LEN($B5)))

**Roibeard** · 12-08-2020, 06:52 PM

Thank you very much your response guys. Fluff's formula is working for me up to the amount of values I need.

This helped me out a lot!

Robert.

**Fluff13** · 12-09-2020, 09:00 AM

Glad to help & thanks for the feedback.

Formula to split text based on a specific delimiter

LinkBack

Thread Tools

Rate This Thread

Display

Formula to split text based on a specific delimiter

Re: Formula to split text based on a specific delimiter

Re: Formula to split text based on a specific delimiter

Re: Formula to split text based on a specific delimiter

Re: Formula to split text based on a specific delimiter

Re: Formula to split text based on a specific delimiter

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to split cell text based on specific characters

[SOLVED] Split Text with Varying Delimiter

[SOLVED] Split and arrange cells based on delimiter

Split contents of a cell based on a delimiter and reformat the splt

[SOLVED] Split text and number field with no delimiter

[SOLVED] Split specific chars in one column to other columns with no delimiter

Split Text using numbers as Delimiter.

Bookmarks

Bookmarks

Posting Permissions