Extracting numbers from a text string

[Mike152]

PROBLEM
I need to extract the Length and the Width (to do some calculations) of each product that comes through my system.

ANALYSIS
The data is held in one Excel cell and looks like this :-
(various lengths of data) – L1111m, W222mm, T33um – (other various lengths data)
or (various lengths of data) – L1m, W2222mm, T333um – (other various lengths data)
or (various lengths of data) – L11m, W2mm, T3333um – (other various lengths data)
or (various lengths of data) – L111m, W22mm, T3um – (other various lengths data)

EXAMPLES
1. Part 768/, - num 34sd23 - L5000m, W63mm, T7um, zxcv
2. Num345 /rt5, - London dfg87 - 4other4 - L250m, W1020mm, T341um, other data
In example 1, I want to end up with ‘5000’ and ‘63’ as numbers.
In example 2, I want to end up with ‘250’ and ‘1020’ as numbers.

POINTS
It’s always Length then Width (then Thickness)
The Length always starts with ‘space dash space L’ and ends with ‘m comma space W’
The Width always starts with ‘m comma space W’ and ends with ‘mm comma space T’
The Length Width (and Thickness) are usually at the very end of the data.

PROBLEMS
I’m having real problems defining the start and endpoint of the Length and Width values because of their variable positions and lengths.
I’ve tried using various combinations of ‘FIND‘, SEARCH’, ‘RIGHT‘, ‘MID‘, ‘LEFT‘, & ‘LEN‘ (and ‘HocusPocus‘ !) but I end up being totally confused and bewildered.

I regard myself as OK with Excel. I have created some complex formulas and I have helped others with their various Excel problems but this is absolutely frightful!

I would be very grateful for some help, thank you, Mike

 

[Peter_SSs]

Mike, try each of these, copied down.

Book1
	A	B	C
1	(various lengths of data) L1111m, W222mm, T33um (other various lengths data)	1111	222
2	or (various lengths of data) L1m, W2222mm, T333um (other various lengths data)	1	2222
3	lengths of data) L11m, W2mm, T3333um (other	11	2
4	data) L111m, W22mm, T3um (other various lengths data)	111	22
5	Part 768/, - num 34sd23 - L5000m, W63mm, T7um, zxcv	5000	63
6	Num345 /rt5, - London dfg87 - 4other4 - L250m, W1020mm, T341um, other data	250	1020
L & W

Cell Formulas
Range	Formula
B1	=RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("m, W",A1)-1),"L",REPT(" ",20)),20)+0
C1	=RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("mm, T",A1)-1),"W",REPT(" ",20)),20)+0

 

[Mike152]

Peter, that woks a treat, absolutely splendid, thank you soooooo much.
I wouldn’t have thought of that in a month of Sundays!
I am very grateful indeed, thanks again, Best Wishes, Mike

 

[Peter_SSs]

Peter, that woks a treat, absolutely splendid, thank you soooooo much.
I wouldn’t have thought of that in a month of Sundays!
I am very grateful indeed, thanks again, Best Wishes, Mike

You are very welcome. Glad to help.