Extract variable data string

[pyrodag]

Hi, I'm trying to extract sale order numbers in a certain format but they have recently jumped up in character length so current formula doesn't work for all cells.

Current formula is =MID(P2,4,7)
This worked fine where number was 0001234567/00100.

Now we have numbers such as 0123456789/00100

I just want to return the number with no leading zeroes and not including the last 6 digits which always begin with a slash.

Can someone help? I seem to do fine using left or right to do one thing but can't get both.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post about half a dozen of the strings you start with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also do you have the TEXTBEFORE function yet?

 

[pyrodag]

Hi & welcome to MrExcel.

Can you post about half a dozen of the strings you start with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also do you have the TEXTBEFORE function yet?

I do not have textbefore, this on my works pc so can't download the add-in.
I have created a small sample and taken an image however.
The cells with slightly longer numbers are incorrectly cut using MID by itself.
Simply has to be without leading zeroes (variable) and without last 6 digits or "/" and after (constant)

 

[Fluff]

Ok, thanks for that, how about

Fluff.xlsm
	A	B
1	0001234567/00100	1234567
2	0123456789/00100	123456789
Main

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,FIND("/",A1)-1)+0

 

[pyrodag]

Ok, thanks for that, how about

Fluff.xlsm
	A	B
1	0001234567/00100	1234567
2	0123456789/00100	123456789
Main

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,FIND("/",A1)-1)+0

Thanks, that has worked fine in getting the correct format showing. Would that have a negative impact on a vlookup that references those cells?

 

[Fluff]

Best way to find out is to try. It really depends on the other data.