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!

 

[jasonb75]

No luck. Anyone else?

That reads as "you had your turn and failed so I have lost faith in your ability" when in fact, the suggestion works, it is the implementation that is flawed.

I've tried to make these examples a little clearer so that you have less chance of breaking them.

How do you want to define which is the correct string? By editing the formula each time, or by giving values elsewhere?

Where A2 holds the first formula,

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

Will return the first string, dragging the formula down to A3 will return the second string, A4, the third, etc.

=IFERROR(TRIM(MID(SUBSTITUTE($A$1,"-",REPT(" ",LEN($A$1))),((3-1)*LEN($A$1))+1,LEN($A$1))),"")

Will return the third string, this version needs to be edited manually.

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

Will start at the third string, then increase to the fourth, fifth, etc as you fill down.

The highlighted values in either of the last 2 versions can be substituted with cell references if required.

I've also added IFERROR to the formula to suppress any #VALUE! errors which would be caused if the value of n exceeds the count of delimited strings in the source cell.

 

[heathclif]

Hi Jason. Haven't lost faith, just wasn't able to cobble it out on my end. I appreciate your post. Let me try again--I'll see if I can get it to work.

Jason, would this formula work if I needed to change the requirements to:

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

where a2 would return the "nth color" as designated by the formula?

Thank you!

 

[heathclif]

Hi Jason, Yes. Your formulas work well with the need to drag down. Trying to see if it would work with the new requirement as mentioned above. Thank you again for the time you put in to help me out.

 

[jasonb75]

I just realised that the third example formula in my last post is wrong, looking back to some of your comments, I think that you will need the second formula, or a slight variation of it.

=IFERROR(TRIM(MID(SUBSTITUTE($A$1,"-",REPT(" ",LEN($A$1))),((n-1)*LEN($A$1))+1,LEN($A$1))),"")


To try and make adjustments a little easier, this is the critical part of the formula for defining the starting point.

((n-1)*LEN($A$1))+1

You can use any method you like to set the value of n, just remember that regardless of the method used, it still needs to be n-1 within the parenthesis.

 

[heathclif]

How do you want to define which is the correct string? By editing the formula each time, or by giving values elsewhere?
.

Editing the formula each time.

If the spreadsheet is as follows:

a1: red-orange-yellow-green-blue-indigo-violet ; b1: yellow
a2: red-orange-yellow-green-blue-indigo-violet ; b2: violet
a3: red-orange-yellow-green-blue-indigo-violet ; b3: blue
a4: red-orange-yellow-green-blue-indigo-violet ; b4: blue

So the formulas appear in b1-b4, but was looking for one formula that I could tweak to return the color needed in column B. For instance, b1, yellow is the third text; b4, blue is the fourth text item...

 

[heathclif]

Hello Jason! I just saw your post, I'll try it out and tell you how it goes. Did not mean to be so glib before. Again, you are appreciated!

 

[heathclif]

Jason you really excel! Thank you! It works! I'll save this post so I can refer to it in the future. Much thanks from a sorcerer's apprentice....!

 

[jasonb75]

Condensing everything from the last few posts, is this something you can work with?

Enter this formula in B1 and fill down to B4

=IFERROR(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN($A$1))),((C1-1)*LEN(A1))+1,LEN(A1))),"")

Then enter 3 in C1, 7 in B2, 4 in B3, 4 in B4.

 

[jasonb75]

Did not mean to be so glib before.

My response was a bit harsh, nearly everyone talks differently in forums to reduce the amount of typing needed, sometimes it's far too easy to misinterpret what is said and take it out of context.

Glad to see you got it working, if you do need any more help with it, don't be afraid to ask, I'll try not to bite again

 

[heathclif]

Just wanted to say back to the board that I was very successful in getting jasonb75's formula to work:

=IFERROR(TRIM(MID(SUBSTITUTE($A$1,"-",REPT(" ",LEN($A$1))),((n-1)*LEN($A$1))+1,LEN($A$1))),"")

(where the n value was adjusted to fit)

jasonb75, i thought your response fine--and really appreciate you pulling me out of this rut and keeping the dialogue going. Vielen Dank!