Identify certain parts in a string

[rumil.kochhar]

Hi ,
I need help to create a macro which can identify certain parts in a string so that i can name it a certain way.

For Example -

I have a plan named "ICICI Prudential - Short Term Investment Plan" but it is written by different people in different ways.Using find function in excel i can spot it immediately stating "ICICI*S*Plan but i am unable to create a function using this technique.

Request for some help as it will save some good time at work.

[mehmetcik]

this is simple.

why do you need a macro?

To select all occurrences or just the first occurrence?

You could put it into a formula in a helper column

[rumil.kochhar]

Thanks mehmetcik for the reply however the reason why i require a macro is because this is only one of the 480 scenarios/plans.I need a solution so that i can break up the plan names to help me create a function which can deal with all possible writing scenario's

just to illustrate there are 46 plans in ICICI prudential of which one i have mentioned below.
If i do not use the way i have stated below i will have to go through all the plans to arrive at the correct plan..

I hope you have understood my concern.

[mehmetcik]

Ok Come back in half an hour and I will have a solution for you.

This will find all occurrences of your text in the workbook.

Now what do you want to do with that list?

Please Login or Register  to view this content.

[rumil.kochhar]

Hi mehmetcik , this is not what i was looking for..This is way to advanced.

What i was looking for was to create a function

For Example

If a Text has ICICI pudential short term plan (words highlighted in bold the function would return a value "ICICI Prudential - Short Term Investment Plan"

similarly for ICICI prudential Long Term Investment Plan , the function would look for ICICI , L* & P* and it would return the value ICICI prudential - Long Term Investment plan.

and for other Birla Sun Life - MIP - Birla S* M* ..So on and so forth.


I would want a function which would populate the correct plan names in another column.

was looking for a function

[rumil.kochhar]

I was earlier using "case is" function and If /Elseif in my functions to populate desired details but that works only on system generated reports which have defined/accurate texts.As i am now working on manually data entered excels which often have spelling mistakes , thus i was looking for some way to ease my work.

[Richard Buttrey]

You'd be better advised to upload a workbook which contains several examples of your data and for each one what value you expect a function to return

Much experience here tells me that 'simple' one or two line narrative examples rarely help and we find ourselves returning to the task when the production workbook isn't delivering what's wanted.

[rumil.kochhar]

Hi as per the moderator's suggestion i have enclosed my data in the attached excel.

The first column "Some Manual Written Plan Name" has the plan names manually written which i need to change as per the plan name mentioned to the right.(Column - Actual Plan Names)

If you notice that in the manually stated data fund is mentioned as plan , Spelling of aggressive is wrong and so on and so forth.

however i can look for the plan by typing the code in the extreme right column in "find" in excel (Ctrl +F)

i need a function to be created so that i can have each plan scenarios populated in the code.

I wan to name this function "Myplans".


I hope now my issue is understood..I am very hopeful that mehmetcik has a solution ready for me

Please help!..Thanks in advance..

[Richard Buttrey]

Hi,

So just to confirm are you saying that you want every column A value to be translated/changed into a column B value?

If so what for instance should the A8 "birla sunlife cash plan" be changed to?

And what are columns C:F and how should they be used. I suspect that you're going to need a fuzzy logic lookup tool - Microsoft have an Excel Add in for this purpose.

[rumil.kochhar]

Exactly , I want all values in column A to be like column B..
Also column c d and e are only provided as a reference to show the breakup of the plans.

Also with regard to the birla sun life cash plan I know there are multiple plans which come under this text , I will place this plan in the end so that it uses other references before it arrives to this plan..

Dear sir my current ask is to get a reference of how this can be done , once I do , I will figure out to find solutions to the next set of problems I face.
I am for a very long time finding a solution to this problem..need your help to close this..

[mehmetcik]

Please Login or Register  to view this content.

[rumil.kochhar]

Hi Mehmetic, I am unable to run this code , it gives a compile error , end if without block if..

Also I require a function ( customized formula) which populates the value in a column next to the column A , ( the values which are mentioned in column B)

Please help, this will certainly save a lot of my time in office.

[jindon]

Try the attached.

Please Login or Register  to view this content.

[rumil.kochhar]

Hi Jindon, thank you so very much , this resolves the issue by 90 % however if there is a issue where it is not able to identify the plan / fund name it shows the default value as the first value of the actual plan names..is there any way we can change this to blank!!

[jindon]

That's the best I can do and I guess what you are asking is almost impossible....

[rumil.kochhar]

Ooh that's sad , well thank you so very much for the help..really appreciate..