How do you find and replace a Wildcard character in Excel?

[Guest]

I have data in a the workbook that contains an asterik in it. I want to
clear that character and leave the remaining ones (or replace it with
something). But a * is considered a wildcard character that signals Excel to
grab everything. How do I find and replace just that?

 

[Chip Pearson]

Precede the * with a ~, e.g., ~*


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Seems like that was too easy for you. And it worked, too. Thanks. How
about this one...The data is coming from another database pulled with an ODBC
connection. The cell shows that Excel merged the data into one cell (in the
other app, someone hit 'enter' to break the data on two lines). Excel shows
it in one cell but with a, for lack of a better term, and small box in
between the two words. I want to keep it in one cell, but get rid of that
little box. Do you know how to do that? Does that even make sense?

 

[Chip Pearson]

Try this: Select the cells in question, go to the Format menu,
choose Cells, then the Alignment tab. There, check the "Wrap
Text".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

I tried it, but it doesn't work. That little box represents a line break
from the original datasource that keep the data in the same field, but
separated it by a line break (maybe the user hit return in between words or
sentences). Do you think there is a way to get rid of that?

 

[Chip Pearson]

Use my CellView addin http://www.cpearson.com/excel/cellview.htm
to determine exactly what character is represented by the square
character, then replace that with CHAR(10) for a line break or a
space.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Gord Dibben]

After import you could try Edit>Replace

what: ALT + 0010(or 0013).....use the numpad to enter the 0010

with: nothing

Replace all.


Gord Dibben Excel MVP

 

[Guest]

Can you explain that further? I couldn't get anything to work in the
find/replace feature.

 

[Dave Peterson]

I can use Edit|replace for the alt-0010 character (it does look like nothing is
in that box, but it works).

(for that alt-0010 (on the number key pad), you can also use ctrl-j.)

But for the alt-0013 character, I use a macro or a worksheet function.

=substitute(a1,char(13)," ")

or as a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ") '<--what's the new character?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm