Formulas to extract nth string with separators

H

heathclif

Board Regular
Joined
Jun 12, 2015
Messages
83
Appreciate any help from the experts here:confused:

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, p:biggrin:r favor!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Last edited:
Upvote 0
:ROFLMAO: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.
 
Upvote 0
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
Click to expand...
Jason's formula returns these results that you posted when put in cells A2-A7.
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
If a cell contains a string, return a value from an array
2 3
Replies
28
Views
572
topi1
T
K
  • Question
Highlight cells whose strings contain the same word
Replies
4
Views
256
kgartin
K
T
  • Question
Sum-Unique - filter
Replies
8
Views
242
Peter_SSs
Peter_SSs
Y
  • Solved
How to drag and and continue formula sequence?
Replies
6
Views
313
StephenCrump
S
B
  • Solved
Find value within a cell with multiple text separated by comma
Replies
3
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,214,411
Messages
6,119,356
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top