1

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?

0

3 Answers 3

2

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.

3
  • 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, 2011 at 13:10
  • @Kevin, to ensure that leading zeros are preserved, you would also have to mark the columns as Text. Jul 22, 2011 at 13:17
  • Good idea, Dave. I didn't even think about that. Thank you!
    – Kevin
    Jul 22, 2011 at 13:41
2

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

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

You must log in to answer this question.

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