# Extracting Numbers from a cell

#### Andrew Watson

##### New Member
HI all. I've trawled through lots posts both here and elsewhere and I can't find an answer applicable to my problem.

I have a cell on one tab that we use as a delivery docket and it contains a package number. In this instance we shall call it cell G11 has a value of 1/26 typed in there for package 1 of 26. I then need to transfer this information onto a delivery label which is on a different tab. Currently this data is split into 2 cells to show a 1 of 26 on the label. I have split the 1/26 by using =LEFT('DATA ENTRY SHEET'!G11,1) and this extracts the first digit and I also have been using the RIGHT version to extract the right number. This isn't an issue until I start to get into double digits area and it only gets the first and last digit. The first picture shows the data field that the package numbers gets entered into (this has to stay like this for ease of use by the end user).

The second screen shot shows the output data field where the above information comes from. I just need to know if there is a single formula that can extract either one or two numbers either side of the "/".

I'm not great on VBA but I already have a VBA that is used to automatically email the spreadsheet once completed.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### footoo

##### Well-known Member

OR :

=SUBSTITUTE(A2,"/"," of ")

#### Dave Patton

##### Well-known Member
try
T202101b.xlsm
GHI
111/26126
12123/88991238899
13
1b
Cell Formulas
RangeFormula
H11:H12H11=LEFT(G11,FIND("/",G11)-1)
I11:I12I11=MID(G11,FIND("/",G11)+1,100)

#### Andrew Watson

##### New Member
HI
try
T202101b.xlsm
GHI
111/26126
12123/88991238899
13
1b
Cell Formulas
RangeFormula
H11:H12H11=LEFT(G11,FIND("/",G11)-1)
I11:I12I11=MID(G11,FIND("/",G11)+1,100)
Dave,

That worked perfectly. Thank you so much for your help.

#### Dave Patton

##### Well-known Member
Thanks for the feedback.

Replies
4
Views
56
Replies
8
Views
90
Replies
1
Views
173
Replies
11
Views
160
Replies
8
Views
122

1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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

### Which adblocker are you using?

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

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