Formula to find the part of string.

[hecgroups]

Hello,

I am looking for a help to write a formula for an string. Let say if the string match another part of string in other column then returns the string mentioned in range. I am looking for a text from new list column B to mater list column B.

If complete text or part of that text found the following should be done.

1. Write the column C value from master list to new list column C (To know which category)

2. Write the complete string from master list to new list column D (To know what is the match of text so that the user can cross check)

3. Write the row number in column E (To know which row number so that the user can goto that row directly)

I hope this will clear yourself to understand what i am trying to achieve.

Thanks for your efforts to help me.

[Søren Larsen]

I think you forgot something...

[AlvaroSiza]

You're going to have to explain this a bit better, Hec. Your post offers no details whatsoever.

Generally speaking, I would encourage you to research =LEFT, =RIGHT, =MID, =LEN; each of which functions under the text/string manipulation subcategory.

[Søren Larsen]

You're going to have to explain this a bit better, Hec. Your post offers no details whatsoever.

Generally speaking, I would encourage you to research =LEFT, =RIGHT, =MID, =LEN; each of which functions under the text/string manipulation subcategory.

And perhaps in combination with =FIND().

[AlvaroSiza]

And perhaps in combination with =FIND().

And further...

=SEARCH, =SEARCHB...

[Cutter]

I am looking for help.

Press the F1 key.

[AlvaroSiza]

Press the F1 key.

Classic. ROFL.

[hecgroups]

I am unable to post a new topic since last 3 days due to some technical problem.

here is the original reply.

Hello,

I am looking for a help to write a formula for an string. Let say if the string match another part of string in other column then returns the string mentioned in range. I am looking for a text from new list column B to mater list column B.

If complete text or part of that text found the following should be done.

1. Write the column C value from master list to new list column C (To know which category)

2. Write the complete string from master list to new list column D (To know what is the match of text so that the user can cross check)

3. Write the row number in column E (To know which row number so that the user can goto that row directly)

I hope this will clear yourself to understand what i am trying to achieve.

Thanks for your efforts to help me.

Sample file is attached for more clarification.

[Pichingualas]

Please Login or Register  to view this content.

I hope that helps.

[hecgroups]

Hai Pichingualas,

Thanks for your solution. Right now i am sick and unable to provide you the feedback. but i will when i feel better.

Thanks once again.

[hecgroups]

Hai Pichingualas,

Your script working as a magic for me. But there is another request for the same.

I have checked on my original data and find the issues which is as follows:-

1. The script should check the word uptill first space then return the service type in column C. If the first word not found then look for second word uptill space. See the example below for more clarification.

Example: Assuming the word i am looking for is NASAL TUBE SUCTION.

So the script should look for NASAL and return the service type in column C if not found then look for word TUBE so on...

2. The service type is repeating more then one i.e. Other Medical Services, Other Medical Services which should not be. If one service type is already assigned then it should skip that type unless it is changed. See the example below for more clarification.

Example: Assuming the word i am looking for is NASAL TUBE SUCTION and found NASAL

So the script should return the service type as Other Medical Services just once not 2 times.

I how this will clear i hope the above explanation is clear to you for finished this issue.

[Pichingualas]

This should be it:

Please Login or Register  to view this content.

[hecgroups]

Thank very much Pichingualas.

It works like magic. I will cross check with other list if i don't find any issues then i will marked this topic as solved and reputation will be added.

Tomorrow i will provide my feedback.

Thanks once again for your valuable script.

[Pichingualas]

Happy to help.

[hecgroups]

Hai Pichingualas

I have an issue. There are some common service types which is available in master sheet but it is not displaying after running the script.

For example:- Consultation, IGM, CBC, etc... these are the common services.

Please see the attached file for more clarification.

[Pichingualas]

Sorry hecgroups I made a small mistake. It's marked in red, check if it works as you want with this correction:

Please Login or Register  to view this content.

[hecgroups]

Thanks for your help. I have one more request. Can I be possible same theory but apply when complete cell value match. Example: If I found urine analysis thn it should return service type.

I hope you have got me what am asking.

[Pichingualas]

If you want to apply when the complete value matches then you will again have the problem with consultation you were saying before... This is because it would have to contain exactly the complete value, so for instance "CONSULTATION – DENTIST" would not match "CONSULTATION FEE - DENTIST SPECIALIST + FILE OPEN" because the word FEE is missing. we could loop it to check that each of the wrods is present but I think it would slow you down. If you don't mind then I can do it, so it's your call.

[hecgroups]

Actually this is my boss call he wants like this. It is said to me if the complete value match then it should return the service type otherwise leave it blank.

But please marked the line where i can change the column of result i.e. service type. At present it is displaying in column C but might me it will be displayed in column M or column P. If this is not possible then doesn't matter. I will manage.

So, if you are searching for complete value then it will not slow down your script.

I hope this is clear to you what i want to achieve.

Thanks for your usual cooperation.

[Pichingualas]

Ok here goes:

Please Login or Register  to view this content.

I hope that's what you wanted.

[hecgroups]

Excellent script. Thanks for your halpe brother. Topic closed and reputation added.