Delete all the values that are to the right side with respect to a value that is stated

[raju.r.083]

Hi all,
Hope all is good.

Actually, I had asked the below mentioned requirement in one of the forum. I had got a solution which was suggested by writing a VBA code.
But I need solution in a formula manner instead of VBA code.
Can anyone please help me in getting the formula ?

The requirement being, I need only those values which are to the left of the value that is highlighted in red colour and including red coloured value.
The values to the right of the red coloured value must not be displayed as shown in Column 'C' (Output Column).
I have entered the values in Column 'C' manually just for my better explanation by considering Column 'B' as the input.

Note:
Just for my explanation I have highlighted one value in each row in Red colour. In actual, there won't be any colour. It will be in default colour as like the other values.
Please see the attached excel file.

Thank you all in advance in reading my request and for your attempts.

[63falcondude]

Formulas cannot see font color.

What is the logic as to why the string should get cut off where you are showing?

[ranman256]

Then what are we searching for in the string?
why is it A2 in line 1, but A2C in line 2?

where do we split it? (with no color)

[Special-K]

Formulas cannot change cell values, you need VBA for that.
You can make a cell equal the result of another cell using a formula, e.g. cell B1 can have a formula based on the value in A1 but the VALUE in A1 can only be changed by VBA.

[raju.r.083]

Hi all,
Thanks a lot for your quick response.
Please see attached excel file for better understanding.
Column 'C' in the attached excel file is based on formula.

Thank you all once again.

[63falcondude]

What's wrong with the formula in column C? It seems to do exactly what you are looking for.

Are you looking for something else?

[raju.r.083]

I need formula to be developed for the workbook named Illustration.
Thank you for the reply.

[AlKey]

Enter formula in C2 and copy down

Please Login or Register  to view this content.

See attached file

[ranman256]

paste this code into a module.
then run :aParseCells

Please Login or Register  to view this content.

[raju.r.083]

Hi all,
Sorry for my delayed response. I am really very sorry.
I am really thankful to all of you to your quick response and to your time in giving me a perfect solution. Thanks a lot!!!!
Thank you Alkey to your awesome formula. You have given the formula which I was looking for and its really working fine.
Thank you Ranman to your code. Its working good as well.

Thank you all!!!!!

[raju.r.083]

Hi Alkey,
Hope you are doing good.

The formula which you have given is working excellently.
Now again I am stuck up. Can you please help me out once again ?

If multiple look up values repeats in a single cell itself, the result must be such that, the value which exists first must only be present in the Output. The rest of the values has to be deleted.
Please see attached the excel file for better clarity.

Thank you once again in advance in solving my problem.