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

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

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

.

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

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

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]

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]

.