Splitting text string using formula...

[Dan1000]

I need to use a formula to split a text string using the commas as dividers with no spaces at either end of each string.

Maximum 4 commas to each string.

Is there a straight forward way of doing this rather than using the data to ccolumns excel command?

Regards

 

[taurean]

Hi Shrivallabha

Remark:
Application.Volatile makes the function inefficient and should only be used if absolutely necessary which is not the case.

Thank you PGC. I will keep that in mind.

 

[Peter_SSs]

Here's another one...

A2 = some string

Entered in B2 and copied across until you get blanks:

=TRIM(MID(SUBSTITUTE(","&$A2&REPT(",",6),",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))

Couldn't that bit be omitted?

 

[jasonb75]

Here is a generic formula for obtaining the Nth field in a delimited text string...

=IF($A1="","",IF(fieldnumber=1,LEFT($A1,FIND(delimiter,$A1)-1),TRIM(MID(SUBSTITUTE($A1,delimiter,REPT(" ",LEN($A1))),LEN($A1)*(fieldnumber-1),LEN($A1)))))

Simply replace the two occurrences of the word "delimiter" with the text that is delimiting the fields (a comma for your current question) and replace the two occurrences of the word "fieldnumber" with the number of the field you want (1 is the field before the first comma, 2 is the field before the second comma, etc.). So, if A1 held the text you wanted to parse and B1 contained the field number you wanted, then this is the formula you would use for you comma delimited question...

=IF($A1="","",IF(B1=1,LEFT($A1,FIND(",",$A1)-1),TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),LEN($A1)*(B1-1),LEN($A1)))))

And I thought my generic formula was a bit on the long side

 

[Rick Rothstein]

And I thought my generic formula was a bit on the long side

Aw, go on ... I could have made it much longer if I had wanted to .

Just to make your formula completely "generic", this is what it would look like...

=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",LEN(A1))),1+(fieldnumber*LEN(A1))-LEN(A1),LEN(A1)))

I plan to steal this formula for future postings of mine... but I will credit you when I do so. :wink:

 

[T. Valko]

Couldn't that bit be omitted?

It seems to work ok without it. I can't remember why it's in there.

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))

 

[Rick Rothstein]

And I thought my generic formula was a bit on the long side

Aw, go on ... I could have made it much longer if I had wanted to .

Just to make your formula completely "generic", this is what it would look like...

=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",LEN(A1))),1+(fieldnumber*LEN(A1))-LEN(A1),LEN(A1)))

I plan to steal this formula for future postings of mine... but I will credit you when I do so. :wink:

Okay, now that I have had a full night's sleep (it was almost 5am when I saw/responded to your message) and had time to study our formulas, I see they were the same... once I removed the two beginning IF statements (meant to protect against empty cells or a fieldnumber of 1) and simply adjusted the field number calculation by 1 in order to handle what those two IF statements were doing. So, we both had the same underlying algorithm, you just saw the -1 adjustment that I missed.

 

[jasonb75]

Okay, now that I have had a full night's sleep (it was almost 5am when I saw/responded to your message) and had time to study our formulas, I see they were the same... once I removed the two beginning IF statements (meant to protect against empty cells or a fieldnumber of 1) and simply adjusted the field number calculation by 1 in order to handle what those two IF statements were doing. So, we both had the same underlying algorithm, you just saw the -1 adjustment that I missed.

I was wondering if you would notice 5am is a good time to catch people off guard.

 

[Dan1000]

Thanks for all your help - I think Rick's formula is the one I need

 

[Dan1000]

I can not get either of the following two formaula to work and not sure why not. I have a text string in one cell I would like to divide using the commas by dragging out the formula...

=TRIM(MID(SUBSTITUTE(","&$A2&REPT(",",6),",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMNS($B1:B1)*LEN($A1))-LEN($A1),LEN($A1)))


Any help please?

 

[Dan1000]

I don't think the formulas in this are ddoing what I need.

I need to count the items before each comma and place them into separate - individual cells after the cell by dragging out.​