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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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,022
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,022
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,053
Office Version
  1. 365
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,022
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,053
Office Version
  1. 365
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,171,278
Messages
5,874,797
Members
433,074
Latest member
alexbecker220

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