formula help for a nood please

[owenstrev]

Can someone please help me extract the first 4 numbers out of a cell, and then separate them as into 2 different columns. The example below is what I am trying to accomplish. I have the first column(O2) and I need it to produce the 2 columns on the right.

Book1
	O	P	Q
1	ABCD	FIRST YEAR	LAST YEAR
2	7521 BN
3	1416 BN	14	16
4	W1013 BN	10	13
5	W0407 BN	04	07
6	W0514 BN	05	14
7	M9908 BN	99	08
8	M9908 BN	99	08
9	W0717 BN	07	17
10	W1316 BN	13	16
11	M0005 BN	00	05
12	0616 BN	06	16
13	0620 BN	06	20
14	0307 BN	03	07
15	9920 RN	99	20
16	0311 RN	03	11
17	W9704 RN	97	04
18	W0513 BN	05	13
19	0721 BN	07	21
20	66187BN	66	18
21	W0305 BN
22	W0310 RN
23	W0310 RN
TYPE RETURN HERE

thank you in advance

 

[StuLux]

If, when there is a letter at the start, it is always a single letter then the following two formulas should work:

In column P - =IF(ISNUMBER(VALUE(LEFT(M3,1))),LEFT(M3,2),MID(M3,2,2))
In Column Q - =IF(ISNUMBER(VALUE(LEFT(M3,1))),MID(M3,3,2),MID(M3,4,3))

 

[owenstrev]

If, when there is a letter at the start, it is always a single letter then the following two formulas should work:

In column P - =IF(ISNUMBER(VALUE(LEFT(M3,1))),LEFT(M3,2),MID(M3,2,2))
In Column Q - =IF(ISNUMBER(VALUE(LEFT(M3,1))),MID(M3,3,2),MID(M3,4,3))

Thank you so much, this almost works, the only issue coming up is when there is a total of 5 numbers, in this case the second formula pulls in the last 3 digits, ex: on row 20 it is returning the value "187" instead of just "18"

 

[owenstrev]

Thank you so much, this almost works, the only issue coming up is when there is a total of 5 numbers, in this case the second formula pulls in the last 3 digits, ex: on row 20 it is returning the value "187" instead of just "18"

Putting this here incase anyone is following the thread,
Column Q - =IF(ISNUMBER(VALUE(LEFT(M3,1))),MID(M3,3,2),MID(M3,4,3))
changed to
Column Q - =IF(ISNUMBER(VALUE(LEFT(M3,1))),MID(M3,3,2),MID(M3,4,2))

fixed it, thank you StuLux for being so smart and helping me out.

 

[StuLux]

Ah yes, I missed the 'rogue' entry that had 5 digits and, in my testing, I didn't have a space in front of this one so the formula worked, you're quite right to change the 3 to 2, glad to have been of help.