|
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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 |
| All times are GMT +1. The time now is 01:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com