Return data after and up to a certain Alphabet Letter??

Good morning all,

I have a predicament with a sheet I am trying to make work but just can seem to get it right. Maybe one of you experts could assist?

We barcode scan stock into excel and need to break the scanned code down to 3 differ blocks denoting, location, part number and quantity. The problem I have is that the character lengths vary and thus a left or right function does not work. Is there a formula that tells excel to return data based on an alphabet letter.

My thoughts would be to do a formula that will return data from the scanned code starting and including "M" (Will always start with M) up to and excluding S or T or H or what ever alphabetical letter comes up and so on.

Below example is what we are trying to achieve. (Location, Stock Code and Quantity needs to populated using a formula)

I have attached an sample sheet of what we need to do

A1 B1 C1 D1
Barcode Scan Location Stock Code Quantity
MB001S13-2121Q009 MB001 S13-2121 Q009
MB01S13-2121Q9 MB01 S13-2121 Q9
MB1S13-2121Q09 MB1 S13-2121 Q09
MB001T7040-1217Q015 MB001 T7040-1217 Q015
MB001T633-1212Q1 MB001 T633-1212 Q1
MB01S116-323232Q119 MB01 S116-323232 Q119

try in B

Formula:

Please Login or Register  to view this content.

In C

Formula:

Please Login or Register  to view this content.

In D

Formula:

Please Login or Register  to view this content.

I do not expect that it will work on other instances..

I rely on the idea where your Location ends with the value of 1 e.g. MB00001

and the QTY starts with Q e.g. Q0007

Going back to JT request. can you post possible scenarios. and also the question of Nicky

is there another way to tell excel where to split, eg location

e.g.

MB00169000N-32Q9 -> in this example from row 7 how do we know or you know where to split?

Edit: Sorry JT... didn't able to refresh before posting.

In B2:
=LEFT(A2,SEARCH("1",A2))

In C2:
=LEFT(SUBSTITUTE(A2,B2,""),SEARCH("Q",SUBSTITUTE(A2,B2,""))-1)

In D2:
=SUBSTITUTE(A2,B2&C2,"")

Hi
try these functions

I have created 3 for completeness, although the Qty column is easy to do with formulas

if you're not familiar with user-defined functions, just copy them into your VBA area as you would a macro and then use them as you would a built-in in Excel function e.g.

=location(A2)

should return

MB1

Please Login or Register  to view this content.

if this returns what you are after, but you don't want to use user-defined formulas, the code could very easily be converted to macros that would return values

Yea, JT...

If A8 can really occur - this is impossible to solve...