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

 

[Leandroarb]

Try:

Considering it as if it were in cell A1.

Mid(A1,Find("H",A1),7)

 

[kweaver]

I believe you're misstating rows and columns because your written statement doesn't match your example.
Do the 7-digits always begin with H? Is there always a second letter after the H? Could there be an H elsewhere in the cell?
Also, is there a LF/CR after the first line in the 1st row of each column?

 

[kweaver]

=MID(A1,FIND(" H",A1)+1,7)

Assumes there are no other strings that begin with " H" that appear before your string.
This would not work for "I Am Healthy HX123Z9"
But will work for "I am healthy HX123Z9"

 

[Leandroarb]

=MID(A1,FIND(" H",A1)+1,7)

Assumes there are no other strings that begin with " H" that appear before your string.
This would not work for "I Am Healthy HX123Z9"
But will work for "I am healthy HX123Z9"

The writing pattern he presented seems to work without the need for '+1'. But all kinds of redundancy to avoid problems are very much appreciated.

 

[kweaver]

My "+1" avoids picking up the space before the "H".

 

[Leandroarb]

My "+1" avoids picking up the space before the "H".

Ok! ?

My sintaxe without '+1' dont too!

? to you and me!

Cheers!

 

[jasonb75]

This would not work for "I Am Healthy HX123Z9"

Changing the formula slightly would eliminate the majority of strings of more or less than 7 characters by sacrificing the upper case H check.

=TRIM(MID(" "&A1&" ",SEARCH(" H?????? "," "&A1&" "),9))

To make the formula more accurate than any of the current suggestions would probably need an array to evaluate each individual character.

 

[Leandroarb]

Well, if there is a hierarchy here I am just an aspiring recruit speaking to two senior officers, but I prefer to be as simple as possible because the "simplicity is the maximum degree of sifistication". Da vinci

Have a nice day!

 

[kweaver]

Jasonb75: gave me a #VALUE! error