Extracting Numbers from a cell

[Andrew Watson]

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.

Any advice is much appreciated.

 

[footoo]

How To Extract Text Before/After Space Or Comma Only In Excel?

When you want to extract the text before or after the first space from the list, do you have a good way to get it done? This tutorial provides some tricky ways to help you extract text before or after the first space or the other delimiter only in Excel.

www.extendoffice.com

OR :

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

 

[Dave Patton]

try

T202101b.xlsm
	G	H	I
11	1/26	1	26
12	123/8899	123	8899
13
1b

Cell Formulas
Range	Formula
H11:H12	H11	=LEFT(G11,FIND("/",G11)-1)
I11:I12	I11	=MID(G11,FIND("/",G11)+1,100)

 

[Andrew Watson]

HI

try

T202101b.xlsm
	G	H	I
11	1/26	1	26
12	123/8899	123	8899
13
1b

Cell Formulas
Range	Formula
H11:H12	H11	=LEFT(G11,FIND("/",G11)-1)
I11:I12	I11	=MID(G11,FIND("/",G11)+1,100)

Dave,

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

 

[Dave Patton]

Thanks for the feedback.