Excel formula to split a string based on delimiter

[kool_mas]

I have strings in a cell such as -

5.02-4.94-4.73-4.94-4.9-3.74-3.57-3.78-3.69-4.97-4.65

There are 10 hyphens separating 11 numbers. Is there an excel formula I can use to pull the number based on the position?

Example- If I want to pull the 3rd numbers, I should get 4.73, 4.65 for 11th number. So, output will be the number based on position specified.

I have a user defined function to do this, but I dont want to use macros. It would be great if this can be done with Excel formula.

Appreciate any help.

Thanks.

 

[kool_mas]

I forgot to mention that there could be missing values between hyphens, i.e I can have a string such as
----------- (with 10 hyphens)- in which case I will have the output for any position as blank.

OR

4.32----5.01------4.90, etc.

 

[Greg Truby]

A1 = your string
B1 = your delimiter
C1 = # of occurance sought

=MID(A1,IF(C1=1,1,SEARCH(CHAR(127),SUBSTITUTE(A1,B1,CHAR(127),C1-1))+1),SEARCH(CHAR(127),SUBSTITUTE(A1&B1,B1,CHAR(127),C1))-IF(C1=1,0,SEARCH(CHAR(127),SUBSTITUTE(A1,B1,CHAR(127),C1-1)))-1)

 

[Greg Truby]

Sorry, I didn't see your "forgot to mention post" before I spent 20 minutes trying to solve the problem based on your original specs. Perhaps another member can help you the rest of the way.

 

[Oaktree]

I'm pretty sure there's a better way to do this, but this seems to work:

=IF(B2=1,LEFT(A1,SEARCH("-",A1)-1),IF(B2=11,RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))),LEFT(REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1,"-","@",B2-1)),""),SEARCH("-",REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1,"-","@",B2-1)),""))-1)))

Where A1 is your hyphenated string and B2 is the number you'd like to return (1 for first value, 2 for second, etc.)

 

[Greg Truby]

Matt, from my testing it doesn't look like yours works on the amended specs either. Were you working off the original specs or the amended ones?

 

[Oaktree]

I was working on the ammended ones.

e.g.

If B2 is 1,2,3,4,5,6,7,8,9,or 11, D1 = ""
If B2 is 10, D1 = 1.01

Book33
	A	B	C	D
1	---------1.01-			1.01
2		10
Sheet1

I only lightly tested this beast though. Perhaps I missed something?

 

[kool_mas]

Greg, I checked your formula, it seems to be working on the amended problem too. I am getting a blank if there is nothing between 2 hyphens (which is correct)

 

[kool_mas]

Thanks Greg and Oaktree, both work well even for blanks between hyphens.