Regular Expression in Excel?

I have a column contains some unwanted value and I want to remove it.

Example data.

%,VApple
%,VBlueBerry,NOrange
%,FPineapple,TWaterMelon,XStrawberry,NMango

Result want to get

Apple
Orange
Mango

It is always the last word after the comma with a character(V or N)

Any help is appreciated!

Well, the fact that there are multiple and varied numbers of commas makes this difficult, I think. (Although, maybe someone smarter than me will say differently.

What I would do is use the Data >> Text to Columns to parse the text into column, then you can use the RIGHT and LEN functions to parse out the rest of the words.

On the other hand ... if you are willing to accept a VBA solution (i.e., a macro), this would be a piece of cake. You simply need a Do Loop that continues until there are no more commas in the string. The RIGHT and LEN functions are available in VBA and they work just like they do in regular Excel. You would use Instr to find the command "in the string".

If your text value is in A1, you can put the following formula in B1:

Please Login or Register  to view this content.

You don't necessarily need to have the PROPER function there; I just added it to match your output. This depends on the "~" not occurring anywhere in your data set. If you do use the "~", pick another character that you do not use.

Scott

I guess I missed the part where terrance wrote

It is always the last word

Good catch, Maistrye!