# Extracting numbers from a string of 3 values with two separators

#### chemicalhaggis

##### New Member
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:
Another

Excel Workbook
ABCD
18 / 9 / 108910
29 / 10 / 1191011
310 / 11 / 12101112
Sheet3

Thanks, that worked. Easy when you know how.

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)

Replies
23
Views
735
Replies
3
Views
361
Replies
1
Views
583
Replies
2
Views
124
Replies
6
Views
181

Threads
1,196,291
Messages
6,014,509
Members
441,825
Latest member
Lade

### 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

### 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