Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

migrated from programmers.stackexchange.com Jul 22 '11 at 13:00

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

3 Answers

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
add comment

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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.