Extracting a Number from the middle of a string

[gooniegirl180]

Hi everyone,

I just can't get my head around how to extract a product length from the middle of a string. Our product codes have a variable number of characters but the length always follows the "/" character. Not a problem, except that some codes have a length with up to 3 decimal places and there may or may not be more string characters after the length.

Examples, and expected results are:

PRODA/5 (want to return "5")
PRODABC/5.355 (want to return "5.355")
PRODABC/5.355SPEC (want to return "5.355")
PRODXY/7000 (want to return 7000)
PRODXY/7.2NOP (want to return "7.2")

and so on. No VBA please, needs to be a worksheet formula.

Thanks in advance for the help.

 

[gooniegirl180]

Hi,

If the formula supplied by admiral100 works for you, Don't change anything, to quickly copy the formula down Column A, with the formula already being in A1.

Method 1, if you may have Blank rows in between your data, And/Or, you want the formula to go Beyond your existing data range in case more data may be added:

1. Select the first cell with formula (i.e., A1), Right click, Copy
2. Hit F5, in the "Reference" box, type A2:A15000 (increase this range if you like)
3. Hold down Shift, click "OK"
4. Control V, the formula is now copied to the end of the range you specified in Step 2 above.

Method 2, if you have No Blank rows in your data set, and Only need to copy the formula to the End of your data set:

1. Select the 1st cell with formula
2. Double Left click on the Cell Handle (little square at the bottom right corner of selected cell)
the formula is now copied to the end of your existing data range.

WELL BLOW ME DOWN. I didn't think I could copy an array formula, but it works!