Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Wildcard
 
Posts: n/a
Default How do you find and replace a Wildcard character in Excel?

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?
  #2   Report Post  
Chip Pearson
 
Posts: n/a
Default

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


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


"Wildcard" wrote in message
...
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?



  #3   Report Post  
Wildcard
 
Posts: n/a
Default

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" wrote:

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


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


"Wildcard" wrote in message
...
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?




  #4   Report Post  
Chip Pearson
 
Posts: n/a
Default

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



"Wildcard" wrote in message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?






  #5   Report Post  
Wildcard
 
Posts: n/a
Default

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" wrote:

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



"Wildcard" wrote in message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?








  #6   Report Post  
Chip Pearson
 
Posts: n/a
Default

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


"Wildcard" wrote in message
...
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" wrote:

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



"Wildcard" wrote in
message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?








  #7   Report Post  
Gord Dibben
 
Posts: n/a
Default

After import you could try EditReplace

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

with: nothing

Replace all.


Gord Dibben Excel MVP


On Mon, 15 Aug 2005 11:19:05 -0700, "Wildcard"
wrote:

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" wrote:

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



"Wildcard" wrote in message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?







  #8   Report Post  
Wildcard
 
Posts: n/a
Default

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

"Gord Dibben" wrote:

After import you could try EditReplace

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

with: nothing

Replace all.


Gord Dibben Excel MVP


On Mon, 15 Aug 2005 11:19:05 -0700, "Wildcard"
wrote:

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" wrote:

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



"Wildcard" wrote in message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?








  #9   Report Post  
Dave Peterson
 
Posts: n/a
Default

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



Wildcard wrote:

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

"Gord Dibben" wrote:

After import you could try EditReplace

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

with: nothing

Replace all.


Gord Dibben Excel MVP


On Mon, 15 Aug 2005 11:19:05 -0700, "Wildcard"
wrote:

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" wrote:

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



"Wildcard" wrote in message
...
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" wrote:

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


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


"Wildcard" wrote in
message
...
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?









--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and replace of word causes change of font formatting jwa90010 New Users to Excel 4 July 22nd 05 08:10 PM
Find and REPLACE within a selection, or column- not entire sheet/. smithers2002 Excel Worksheet Functions 4 April 21st 05 04:45 PM
Find and Replace - Quickest Option? Lindsey M Excel Worksheet Functions 1 March 8th 05 12:34 PM
find replace cursor default to find box luffa Excel Discussion (Misc queries) 0 February 3rd 05 01:11 AM
VB Find and Replace Bony_Pony Excel Worksheet Functions 10 December 6th 04 06:45 PM


All times are GMT +1. The time now is 11:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"