excel search cell from right to left for character 1

[firegambler]

Hi,

my challenge looks like this:
I have several values in a column like:
**Manufacturing
**Warehousing
***VARIABLE COSTS
****OPERATING PROFIT

I am longing to eliminate the asterisks.
So I would like to search the cells from the right for the first asterisk to appear so that I could do something like
=right(A4,searchfromright("*",A4))

Is there a way to get rid of those asterisks without having a ton of nested if-statements?

Any tip is greatly appreciated.

Thanks in advance!

regards

firegambler

 

[KenWright]

Select all the data and do Edit / Replace, replacing ~* with nothing.

Note the tilde in front of the asterisk

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Or if you really want to go the formula route then try SUBSTITUTE()

=SUBSTITUTE(A1,"*","")

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firegambler]

Hi,

thanks for your response.
I have to do it using a formula...
The thing is that there are from 0 to 4 asterisks in the cell and I need to delete them all no matter how many there are.
Substitute unfortunately needs parameters on how many characters to substitute but that value may vary from 0 to 4.

Any other ideas?



regards

firegambler

 

[KenWright]

No it doesn't, that parameter is optional and if not used SUBSTITUTE will get rid of all of them.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firegambler]

Wooohooo!
That did it.

I work on a german version of Excel,
and since 'substitute' in German means ERSETZEN
i thought that the function ERSETZEN would do it but that was the one that needed additional parameters.
Then I found out that the equivalent to the substitute-function is called WECHSELN in german. That did it.

Thanks a ton!

regards

firegambler

 

[KenWright]

You're very welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------