Extracting numbers from a string of 3 values with two separators

chemicalhaggis

New Member
Joined
Mar 31, 2013
Messages
2
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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))
 
Last edited:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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
Back
Top