Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm not an Excel guru by any means, but one of our users has asked me to come up with a way to extract certain data from a column cells in an Excel spreadsheet. Given the following examples, how could I extract the middle numbers and end numbers from the following values?

103-4-25  (I'd want to extract 4 and 25)
99-07-113 (I'd want to extract 07 and 113)
75-1-004  (I'd want to extract 1 and 004)

I guess what I'm looking for is a couple of formulas that I can paste in a couple of cells to grab this data. Can someone point me in the right direction?

share|improve this question

migrated from Jul 22 '11 at 13:00

This question came from our site for professional programmers interested in conceptual questions about software development.

up vote 2 down vote accepted

You can use the 'Text to Columns' function (in Excel 2007, under tab 'data').

Choose 'Delimited' Set Delimiter to 'other' and enter an hyphen. Finish.

share|improve this answer
Awesome! This is exactly what I needed. I showed the user how to do this and they were very happy. I greatly appreciate it! – Kevin Jul 22 '11 at 13:10
@Kevin, to ensure that leading zeros are preserved, you would also have to mark the columns as Text. – Dave DuPlantis Jul 22 '11 at 13:17
Good idea, Dave. I didn't even think about that. Thank you! – Kevin Jul 22 '11 at 13:41
Happy to know, thanks! – Tiago Cardoso Jul 22 '11 at 13:45

Text to Columns is the easiest way to do this, but it is possible to do with formulas.

Assuming that your data is in column A, to make the formulas easy to follow, we'll split things into separate columns.

Column B has the position of the first hyphen:

=FIND("-", A1)

Column C has the position of the second hyphen:

=FIND("-", A1, FIND("-", A1)+1)

Column D has the middle number. Use the first version to keep leading zeroes in the value and the second version to remove them.

=MID(A1, B1+1, C1-B1-1)  
=VALUE(MID(A1, B1+1, C1-B1-1))

Column E has the end number; again, use the first version to keep leading zeroes and the second version to remove them.

=MID(A1, C1+1, LEN(A1)-C1)
=VALUE(MID(A1, C1+1, LEN(A1)-C1))
share|improve this answer
Thanks, Dave! I appreciate it! – Kevin Jul 22 '11 at 13:42

For each cell in the column, split the string at "-" and take index 1 and 2

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .