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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kool_mas

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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.)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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?
 

kool_mas

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

Forum statistics

Threads
1,136,926
Messages
5,678,616
Members
419,776
Latest member
mikelowski

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
Top