Split text

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hi,

I have a list with credit ratings from which a lot are extended with /*
+ or /*-, as these are confusing I would like to delete them.
My question is if there is a formula to exclude these signs from the
actual rating. For example: AA- /*- needs to become AA-

Thanks

Regards,
Robert
 
Hi,

I have a list with credit ratings from which a lot are extended with /*
+ or /*-, as these are confusing I would like to delete them.
My question is if there is a formula to exclude these signs from the
actual rating. For example: AA- /*- needs to become AA-

Thanks

Regards,
Robert
Click to expand...

Because of word wrap, I couldn't tell if there were two or three optional
extensions; but the following (for two) should give you the idea:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"/*-",""),"/*+",""))
--ron
 
You could use Edit|replace

Select the range to fix
edit|replace (ctrl-h)
what: /~*
with: (leave blank)
replace all

that tilde in ~* tells excel not to treat the asterisk as a wildcard.
 
Hello Robert,

If there are many records, you can go for Replace command in Edit menu. This
will save considerable amount of file size and performance. However, if you
want to exclude "/*-" or "/*+" only in few places, you can go for the formula
suggested by Mr. Ron Rosenfeld.

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"/*-",""),"/*+",""))

This formula will remove all the unwanted (extra) spaces and the symbols as
well.
 
Back
Top