Removing the ' * ' symbol from strings in bulk.

[NeverHappyMike]

Hi


I've a list of country names with * at the end of some them. This list is generated each year, and some country's lose the * depending on the year.


I'm going to extract data from each year using INDEX/MATCH so I need to remove the * at the end of each country.


I've found and tried this:

=IF(RIGHT(L13,)="*",LEFT(L13,LEN(L13)-1),L13)

But it's not worked.


The * seems to be the problem. Any ideas?

 

[NeverHappyMike]

I think I cracked it. I needed to remove the stray ', ' in the first part of

RIGHT(L13

,).

 

[Colin Legg]

Hi,


Yes, just to explain why....


The syntax of the RIGHT() worksheet function is:

RIGHT(text,num_chars)


RIGHT(L13,) is the equivalent of RIGHT(L13,0) which isn't what you want.

RIGHT(L13) is the equivalent of RIGHT(L13,1) because if num_chars is omitted then the default value is 1.

 

[NeverHappyMike]

Thanks Colin.


I was wondering why a simple ',' would cause the problem.

 

[shrivallabha]

You can also do "FIND AND REPLACE" on the original column

1. Select the column.

2. Invoke FIND and REPLACE dialog [CTRL+H]

3. FIND: ~*

4. REPLACE: Keep Blank

and the replace all.