Formulas to extract nth string with separators

[heathclif]

Appreciate any help from the experts here

In a cell, I may need to extract a string divided by separators. Sometimes I need the first, sometimes the second, sometimes the nth.


a1: red-orange-yellow-green-blue-indigo-violet


formula should return:


a2: red
a3: orange
a4: yellow
a5: green
a6: blue
a7: indigo


Help, pr favor!

 

[Joe4]

I would probably just use "Text to Columns" to split all of these values into different columns going across the row, and then use Transpose to copy them down the column instead of cross the row.

 

[jasonb75]

Try this in A2, then copy down to A7

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

the nth string is defined by ROWS(A$2:A2)

 

[heathclif]

My original ss uses text to columns and is getting kind of hard to use. Embedding the formulas will allow to data to be formatted in native format.

Really, I should have just said that I'm looking for a formula can be customized to return the "nth" string from the cell.

 

[heathclif]

ADVERTISEMENT

Hi Jason, I'll give this a try.

 

[heathclif]

No luck. Anyone else? A formula that can return the nth occurrence of a text between delimiters?

a1: red-orange-yellow-green-blue-indigo-violet
a2: (formula entered here should be able to return: red, orange, yellow..., user would just need to change the formula to nth value to accommodate)

I'm guessing there's no easy way to do this without vB? Been searching for awhile.

 

[Joe4]

ADVERTISEMENT

I found your question a little confusing.

Do you want to find just one results, the nth occurrence, whatever you decide it is at that time?

- or -

Do you want to return the results you posted in your original post:

formula should return:


a2: red
a3: orange
a4: yellow
a5: green
a6: blue
a7: indigo

Jason's formula returns these results that you posted when put in cells A2-A7.

 

[heathclif]

Hi Joe! Yes, I need the following:

"One result, the nth occurrence, whatever you decide it is at that time"

Sorry if I was unclear.

Thank you!

 

[Joe4]

Take a look here: How to find nth occurrence (position) of a character in text string in Excel?

 

[heathclif]

Looking...