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]

I didn't realise that the sample had line breaks with no spaces, thought it just got wrapped somewhere in the process of copy and paste.

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

 

[kweaver]

Jasonb75: good solution.

 

[ranjith2523]

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?

Really sorry for the confusion caused. Yes what you are saying is correct. The paragraph will be in A1, B1, C1 the formula needs to apply in A2, B2, C2. Sorry again for the confusion caused.

 

[ranjith2523]

Try:

Considering it as if it were in cell A1.

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

A big thanks for your help here.

This formula worked as expected but here I just experienced another scenario where the value appears in beginning instead of Middle. Here in cell A2 the result appearing as #VALUE!, I understand we used Mid formula hence we are not getting the result as expected. I didn't expect this scenario hence I was not mentioned about it initially.

HH514A1 1 Year CMS Enhanced Support 24x7 US DP for Pkg/Band = RP * 1	1 Year CSP 9x5 Basic H6EV0A1 Support
#VALUE!	H6EV0A1

It is possible to modify the formula to get the expected result even the value appears in the beginning instead of the middle.

Thanks for all your help in advance.

Regards,
Ranjith

 

[ranjith2523]

I didn't realise that the sample had line breaks with no spaces, thought it just got wrapped somewhere in the process of copy and paste.

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

Thank you so much for your help. I have no words to say this formula works as expected

 

[ranjith2523]

A big thanks for your help here.

This formula worked as expected but here I just experienced another scenario where the value appears in beginning instead of Middle. Here in cell A2 the result appearing as #VALUE!, I understand we used Mid formula hence we are not getting the result as expected. I didn't expect this scenario hence I was not mentioned about it initially.

HH514A1 1 Year CMS Enhanced Support 24x7 US DP for Pkg/Band = RP * 1	1 Year CSP 9x5 Basic H6EV0A1 Support
#VALUE!	H6EV0A1

It is possible to modify the formula to get the expected result even the value appears in the beginning instead of the middle.

Thanks for all your help in advance.

Regards,
Ranjith

Kindly do not investigate further on this...

I got an expected result from the below formula which provided by "jasonb75"

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

Thank you so much for your time and help.

Regards,
Ranjith

 

[ranjith2523]

I didn't realise that the sample had line breaks with no spaces, thought it just got wrapped somewhere in the process of copy and paste.

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

I just really wanted to understand how this formula works. If you have time then could you please split out the formula steps and explain to me how it really works?

Please don't think I am insisting you, I am just requesting you since I want to learn and teach other peoples.

If you don't have time then please ignore this message.


Regards
Ranjith

 

[jasonb75]

You would be better off learning what each of the individual functions does before attempting to understand a formula that uses all of them.

SUBSTITUTE(A1,CHAR(10) is used because you have line breaks (2 lines in the same cell) directly after some of the codes to extract. This replaces the line breaks with spaces to make the codes easier to identify. This appears twice in the formula because the first and second parts need to be identical, if we only change one then the rest will not work.

" "& before SUBSTITUTE and &" " after it add a space to the start and end of the text, without this the formula would not be able to correctly identify the codes if they were at the beginning or end of the text in the cell.

Search looks for a string of 9 characters in the format of " h?????? ", or h followed by any 6 characters ans surrounded by spaces. MID splits that from the rest of the text and TRIM removes the spaces to clean it up.

As I said in one of my earlier replies, it is not perfect. It doesn't check for numbers in the string, so any 7 letter word beginning with H would be detected, as would 2 consecutive 3 letter words.
Making allowances for such things would make the formula significantly more complicated and borderline impractical.

 

[ranjith2523]

You would be better off learning what each of the individual functions does before attempting to understand a formula that uses all of them.

SUBSTITUTE(A1,CHAR(10) is used because you have line breaks (2 lines in the same cell) directly after some of the codes to extract. This replaces the line breaks with spaces to make the codes easier to identify. This appears twice in the formula because the first and second parts need to be identical, if we only change one then the rest will not work.

" "& before SUBSTITUTE and &" " after it add a space to the start and end of the text, without this the formula would not be able to correctly identify the codes if they were at the beginning or end of the text in the cell.

Search looks for a string of 9 characters in the format of " h?????? ", or h followed by any 6 characters ans surrounded by spaces. MID splits that from the rest of the text and TRIM removes the spaces to clean it up.

As I said in one of my earlier replies, it is not perfect. It doesn't check for numbers in the string, so any 7 letter word beginning with H would be detected, as would 2 consecutive 3 letter words.
Making allowances for such things would make the formula significantly more complicated and borderline impractical.

Thank you so much for spending your precious time and responded to my message.

Mow more or less I can understand how the formula works.

Thanks again.

Regards,
Ranjith

 

[ranjith2523]

You would be better off learning what each of the individual functions does before attempting to understand a formula that uses all of them.

SUBSTITUTE(A1,CHAR(10) is used because you have line breaks (2 lines in the same cell) directly after some of the codes to extract. This replaces the line breaks with spaces to make the codes easier to identify. This appears twice in the formula because the first and second parts need to be identical, if we only change one then the rest will not work.

" "& before SUBSTITUTE and &" " after it add a space to the start and end of the text, without this the formula would not be able to correctly identify the codes if they were at the beginning or end of the text in the cell.

Search looks for a string of 9 characters in the format of " h?????? ", or h followed by any 6 characters ans surrounded by spaces. MID splits that from the rest of the text and TRIM removes the spaces to clean it up.

As I said in one of my earlier replies, it is not perfect. It doesn't check for numbers in the string, so any 7 letter word beginning with H would be detected, as would 2 consecutive 3 letter words.
Making allowances for such things would make the formula significantly more complicated and borderline impractical.

When I tried to understand your formula I just removed the TRIM, removed the spaces before and after the search team ("H??????") then reduced the Mid-length to 7 but still it is working great and correctly identify the codes if they were at the beginning or end of the text in the cell.

=MID(" "&SUBSTITUTE(B1,CHAR(10)," ")&" ",SEARCH("H??????"," "&SUBSTITUTE(B1,CHAR(10)," ")&" "),7)

Can you give me one example in what scenario the above-modified formula will not work or will throw wrong or error values ?

Thanks for your help in advance.

Regards,
Ranjith