Populating a column by extracting a variable code from another column

[pedexe90]

Hi everyone,

I would like column D:D to be populated with a function rather than manually. This column contains H codes taken from E:E. The codes are in this format: H0xx-H9xx (4 characters, first character is fixed as H the second character varies from 0 to 9 and the third and fourth characters are random).

I would like one formula to extract the code and return it to column D as seen in the attached file.

Many many thanks.

https://1drv.ms/x/s!AgQdYsSdg735eSFFPKUE3l_bjqM

 

[pedexe90]

You may also enhance my formula, I don't know how to incorporate an OR function to this so it only returns H0xx.

=IFERROR(MID(E2,SEARCH({"H0","H1","H2","H3","H4","H5","H6","H7","H8","H9"},E2),4),"")

 

[jtakw]

Hi,

This should do what you need:

Book1
	D	E
2	H011	Music-Music Tour-HS-(H011)
3	H012	Music-MS-Honor Boys Choir-(H012)
4	H013	Music-MS-Honor Band-(H013)
5	H014	Music-HS Single Ensemble (H014)
6	H015	Music-HS-Honor Combined Ensembles (H015)
7		0
8		0
9		0
10		0
11	H018	Language - French Trip H018
12	H196	French Field Trip (H196)
13	H112	Languages-Spanish Trip - H112
14		0
15	H020	Music-MS-Honor Mixed Choir-(H020)
16	H006	China-SL-Trip-(H006-19S)
17	H007	Lunch/Drinks Program H007
18		Fall L.S. After Sch. Program
19		Winter L.S. After Sch. Program
20		Spring L.S. After Sch. Program
21		Summer School(exclude Discount)
22		Spring Camp- J Smithies
23		Spring School
24	H030	Theatre Arts-HS-ISTA Festival-(H030)
25	H031	Theatre Arts-MS-ISTA Festival(H031)
Sheet66

Cell Formulas
Range	Formula
D2	=MID(E2,MIN(SEARCH({"H0","H1","H2","H3","H4","H5","H6","H7","H8","H9"},E2&"H0H1H2H3H4H5H6H7H8H9")),4)

D2 formula copied down.