Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
Hello all,
I have in Column A customers that have a * and two extra spaces.These customers that contain such thing is the total for that customer . Example, Column A * Test Tech * tech is this Is it possible to replace the *Spacespace to Total?Exa, Total Test Tech Total tech is this If I do the Find Replace, it will cause some errors because of the * and space. Please advise any help. Thanks, Juan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
Hi Juan,
This line of code works for me: Worksheets("Sheet1").Columns(1).Replace "* ", "Total " -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Juan wrote: Hello all, I have in Column A customers that have a * and two extra spaces.These customers that contain such thing is the total for that customer . Example, Column A * Test Tech * tech is this Is it possible to replace the *Spacespace to Total?Exa, Total Test Tech Total tech is this If I do the Find Replace, it will cause some errors because of the * and space. Please advise any help. Thanks, Juan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
You could try something like this:
Dim ChangeRange as Range Dim Cell as Range Set ChangeRange = ActiveSheet.Range("A1:A100") For each Cell in ChangeRange If Left(Cell,3) = "* " then Cell.Value = "Total " & Trim(Right(Cell,Len(Cell)-3)) End If Next Cell -----Original Message----- Hello all, I have in Column A customers that have a * and two extra spaces.These customers that contain such thing is the total for that customer . Example, Column A * Test Tech * tech is this Is it possible to replace the *Spacespace to Total?Exa, Total Test Tech Total tech is this If I do the Find Replace, it will cause some errors because of the * and space. Please advise any help. Thanks, Juan . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
Hi Juan,
In order to replace the asterisk (*) symbol. I beleive tha you need to use use the two symbol combination ~* If you use the asterisk alone, it will be interpreted as a wildcard. You can do this manually or in vba: Sub ReplaceAsteriskSpace() Dim cell As Range For Each cell In Selection cell.Replace What:="~*", Replacement:="Total", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False cell.Value = Application.Trim(cell.Value) Next End Sub --- Regards, Norman "Juan" wrote in message ... Hello all, I have in Column A customers that have a * and two extra spaces.These customers that contain such thing is the total for that customer . Example, Column A * Test Tech * tech is this Is it possible to replace the *Spacespace to Total?Exa, Total Test Tech Total tech is this If I do the Find Replace, it will cause some errors because of the * and space. Please advise any help. Thanks, Juan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
In the Find What box, type ~* followed by 2 spaces
In the Replace With box, type Total and 1 space On Wed, 28 Jul 2004 12:25:13 -0700, "Juan" wrote: Hello all, I have in Column A customers that have a * and two extra spaces.These customers that contain such thing is the total for that customer . Example, Column A * Test Tech * tech is this Is it possible to replace the *Spacespace to Total?Exa, Total Test Tech Total tech is this If I do the Find Replace, it will cause some errors because of the * and space. Please advise any help. Thanks, Juan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
Jake Marx wrote:
This line of code works for me: Worksheets("Sheet1").Columns(1).Replace "* ", "Total " My fault - as Myrna and Norman pointed out, you should preface the * with a ~: Worksheets("Sheet1").Columns(1).Replace "~* ", "Total " -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace with total
Hello all,
sorry for late reply. Just wanted to thank all of you who contributed help. I tested the info and works great. So once again thanks to all of you for taking the time. Have a greatt end of week. Juan -----Original Message----- Jake Marx wrote: This line of code works for me: Worksheets("Sheet1").Columns (1).Replace "* ", "Total " My fault - as Myrna and Norman pointed out, you should preface the * with a ~: Worksheets("Sheet1").Columns (1).Replace "~* ", "Total " -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
WHY DO HASHES REPLACE NUMERICAL TOTAL | Excel Discussion (Misc queries) | |||
Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions |