Need Help On Formula

[ranjith2523]

Hi Friends,

I need the formula to get a specific value from a cell. I tried SEARCH, MID, LEFT, RIGHT but none of the formulae is working for me.

In Cell A1, I have a paragraph and likewise in cell A2 and A3. In cell B1, B2, B3 I need to apply a formula to pick the 7 digit value which starts with H and ends with any number. The value will be always 7 digits and start with H and ends with a number.

Foundation w/RTNV Support HJ804A2 2Y DSP 9x5	Rotate w/RTNV Support HK715A1 2Y DSP	Basic HA611A5 Support 2Y DSP
HJ804A2	HK715A1	HA611A5

I hope someone will help me to find out a solution here.

Thanks for all your help in advance.

Regards,
Ranjith

 

[jasonb75]

Can you give me one example in what scenario the above-modified formula will not work

Any cell with 'H' or 'h' before the correct string to extract.

As I said in an earlier reply, my formula is not perfect, it can not see the difference between numbers and letters, so any combination of 7 characters beginning with H and surrounded by spaces would still be a problem, this can be seen in the last entry below. A 'Fix' for this would require a much more complicated formula, if it is even possible.

Book1
	B	C
1	without spaces H6EV0A1	hout sp
2	with spaces H6EV0A1	H6EV0A1
Sheet2

Cell Formulas
Range	Formula
C1	C1	=MID(" "&SUBSTITUTE(B1,CHAR(10)," ")&" ",SEARCH("H??????"," "&SUBSTITUTE(B1,CHAR(10)," ")&" "),7)
C2	C2	=TRIM(MID(" "&SUBSTITUTE(B2,CHAR(10)," ")&" ",SEARCH(" H?????? "," "&SUBSTITUTE(B2,CHAR(10)," ")&" "),9))