Excel formula to split a string based on delimiter

kool_mas

Board Regular
Joined
Oct 7, 2004
Messages
63
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
I'm pretty sure there's a better way to do this, but this seems to work:

Code:
=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.)
 
Upvote 0
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?
 
Upvote 0
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
ABCD
1---------1.01-1.01
210
Sheet1


I only lightly tested this beast though. Perhaps I missed something?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,217,440
Messages
6,136,630
Members
450,022
Latest member
Joel1122331

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