Move data right and up one cell....

[Marklarbear]

Hi All,

Using Excel 2000 on Windows XP.

I have the following example:

Excel Workbook
	A	B
1	List	Code
2	Student, Loan
3	e31713
4	Car, Steering
5	e31697
6	Rough, Ride
7	e38445
8	BAMFORD (c), Jones
9	c34334
10	Tornado, Shoe
11	e38611
12
13
14
15	EVANS (c), NICK
16	c04310
17	Fozzy, Bear
18	e31731
19	LUITJES (c), STEPHEN
20	c31141
21	OWENS, clive
22	c34418
23
24
25	Scopion, Mark
26	c34967
27	SOMERS, DAVe
28	e32153
29	WEEKS, ALEX
30	e38617
Sheet1

Excel 2000

What I need to do is create a formula in column B that identifies the C / E number in column A, and records the C / E number next to the item description above it in column B. to get the following:

Excel Workbook
	A	B
1	List	Code
2	Student, Loan	e31713
3	e31713
4	Car, Steering	e31697
5	e31697
6	Rough, Ride	e38445
7	e38445
8	BAMFORD (c), Jones	c34334
9	c34334
10	Tornado, Shoe	e38611
11	e38611
12
13
14
15	EVANS (c), NICK	c04310
16	c04310
17	Fozzy, Bear	e31731
18	e31731
19	LUITJES (c), STEPHEN	c31141
20	c31141
21	OWENS, clive	c34418
22	c34418
23
24
25	Scopion, Mark	c34967
26	c34967
27	SOMERS, DAVe	e32153
28	e32153
29	WEEKS, ALEX	e38617
30	e38617
Sheet1

Excel 2000

Any help will be greatly appreciated.

 

[Robert Mika]

Try this:
=IF(AND(OR(LEFT(TRIM(A2),1)={"e","c"}),ISNUMBER(VALUE(MID(TRIM(A2),2,1)))),A2,"")

copy down

 

[Marklarbear]

Brilliant - exactly what I need...

Thankyou Robert ..