Extracting numbers from a string of 3 values with two separators

[chemicalhaggis]

Hi All
I am trying to extract 3 numbers to separate cells, which are outputted into an excel sheet in the form "A / B / C" (where A B and C are 1 or two digit numbers). Due to the spaces between the "/" delimiter I can use the LEFT and RIGHT functions specifying that 2 digits are to be returned but I don't know how to extract the central value as it's position in the string may change depending on whether A and B are one or two digit numbers.

Many thanks for any help.

Example strings
8 / 9 / 10
9 / 10 / 11
10 / 11 / 12

 

[Rick Rothstein]

Here is a generalized single formula solution that you can use (it will work if your numbers have more than one or two digits and it will also work if you have more than three delimited numbers per cell). I have assumed your data starts in A2, so put this formula in B2 and copy it across to D2, then copy B2:D2 down as far as you would like...

=--TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMNS($A1:A1)*99-98,99))

 

[VoG]

Another

Excel Workbook
	A	B	C	D
1	8 / 9 / 10	8	9	10
2	9 / 10 / 11	9	10	11
3	10 / 11 / 12	10	11	12
Sheet3

 

[chemicalhaggis]

Thanks, that worked. Easy when you know how.

 

[Rick Rothstein]

Thanks, that worked. Easy when you know how.

It is not clear from you message which response you are referring to, so I will say thank you for both of us. If, however, you were referring to mine, I have a minor simplification to offer for my formula. Since I am using the double unary (the two minus signs, which are the same as multiplying by minus one twice... -1*-1=1, hence value doesn't change) in order to convert the text to a real numerical value, the leading and trailing spaces get absorbed in the conversion so that we do not need the TRIM function to remove them for us. This formula will work the same as the one I posted earlier (and it will be a miniscule amount quicker as well due to the elimination of that one function call)...

=--MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMNS($A1:A1)*99-98,99)