How Do I Trim and Format This Text?

  • Thread starter Thread starter JessiRight77
  • Start date Start date
J

JessiRight77

I have a series of cells that contains text in the format:

*My!Whole!Name*

Is there some way to remove the beginning/ending asterisks and the
exclamation points so that the cells always display as follows:

My Whole Name

Any help would be greatly appreciated!

Thanks,
Jessi
 
Hi!

Select the range of cells in question.

Goto Edit>Replace

Find What: ~*
Replace With: nothing, leave this blank
Replace All

Then repeat

Find What: !
Replace With: enter a space by hitting the spacebar
Replace All
the cells always display as follows
Click to expand...

If that data is imported/copied from another source then you just have to
live with it and do the Edit>Replace regularly.

Biff
 
Thanks for your help, Bif.

However, this text is constantly changing... so I need for the
replacements to occur automatically. I was hoping that a TRIM or
SUBSTITUTE function would work, but I can't figure it out.

Jessi
 
Don't think SUBSTITUTE see it as a wildcard so

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

--
Regards,

Peo Sjoblom

(No private emails please)
 
Hi Jess,
A slightly different approach would be to change both the asterisk and the
exclamation point to spaces and then TRIM the result.

B2: =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"*"," "),"!"," ")

instead of the very specific example where asterisks at end get removed
B2: =SUBSTITUTE(SUBSTITUTE(A2,"*",""),"!"," ")

While the example looks to be a very carefully created example such as
a class problem, it presents the possibilites to create some additonal
examples of related macro usage, so I've created a web page to cover
those as well.

Replace Worksheet Function and in VBA
http://www.mvps.org/dmcritchie/excel/replace.htm
 
Back
Top