| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Replace to remove part of a string
Hi,
I have a number of cells that end with ;Color=12342. For example - Alan;Color=12342 Jane;Color=12342 Clare;Color=12342 Ian;Color=12342 I want to replace ";Color=12342" with "". So that the 4 cells contain - Alan Jane Clare Ian The following code doesn't work (replaces nothing) - range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); And this line of code inserts a space at the end of each cell, which I don't want - range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); Why does the first line of code above not work? Thanks, Barry. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Replace to remove part of a string
From the Recorder:
Cells.Replace What:=";Color=12342", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False -- Gary''s Student - gsnu2007k " wrote: Hi, I have a number of cells that end with ;Color=12342. For example - Alan;Color=12342 Jane;Color=12342 Clare;Color=12342 Ian;Color=12342 I want to replace ";Color=12342" with "". So that the 4 cells contain - Alan Jane Clare Ian The following code doesn't work (replaces nothing) - range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); And this line of code inserts a space at the end of each cell, which I don't want - range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); Why does the first line of code above not work? Thanks, Barry. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Replace to remove part of a string
Hi,
You don't need code you can use =LEFT(A1,FIND(";",A1)-1) But if you want code try Sub sonic() lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Range("A1:A" & lastrow) On Error Resume Next For Each c In myrange c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1) Next End Sub Mike " wrote: Hi, I have a number of cells that end with ;Color=12342. For example - Alan;Color=12342 Jane;Color=12342 Clare;Color=12342 Ian;Color=12342 I want to replace ";Color=12342" with "". So that the 4 cells contain - Alan Jane Clare Ian The following code doesn't work (replaces nothing) - range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); And this line of code inserts a space at the end of each cell, which I don't want - range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); Why does the first line of code above not work? Thanks, Barry. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Replace to remove part of a string
There is need to specify a starting position of 4 in your Instr function
call in this line of code... c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1) ....the default start position of 1 would work fine. I would use this instead... c.Value = Left(c.Value, InStr(c.Value, ";") - 1) As a matter of fact, if the person's name was short, like Jo, then your original code would not remove the desired part. Rick "Mike H" wrote in message ... Hi, You don't need code you can use =LEFT(A1,FIND(";",A1)-1) But if you want code try Sub sonic() lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Range("A1:A" & lastrow) On Error Resume Next For Each c In myrange c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1) Next End Sub Mike " wrote: Hi, I have a number of cells that end with ;Color=12342. For example - Alan;Color=12342 Jane;Color=12342 Clare;Color=12342 Ian;Color=12342 I want to replace ";Color=12342" with "". So that the 4 cells contain - Alan Jane Clare Ian The following code doesn't work (replaces nothing) - range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); And this line of code inserts a space at the end of each cell, which I don't want - range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); Why does the first line of code above not work? Thanks, Barry. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Replace to remove part of a string
Thanks rick I don't know how that crept in
Mike "Rick Rothstein (MVP - VB)" wrote: There is need to specify a starting position of 4 in your Instr function call in this line of code... c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1) ....the default start position of 1 would work fine. I would use this instead... c.Value = Left(c.Value, InStr(c.Value, ";") - 1) As a matter of fact, if the person's name was short, like Jo, then your original code would not remove the desired part. Rick "Mike H" wrote in message ... Hi, You don't need code you can use =LEFT(A1,FIND(";",A1)-1) But if you want code try Sub sonic() lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Range("A1:A" & lastrow) On Error Resume Next For Each c In myrange c.Value = Left(c.Value, InStr(4, c.Value, ";", 1) - 1) Next End Sub Mike " wrote: Hi, I have a number of cells that end with ;Color=12342. For example - Alan;Color=12342 Jane;Color=12342 Clare;Color=12342 Ian;Color=12342 I want to replace ";Color=12342" with "". So that the 4 cells contain - Alan Jane Clare Ian The following code doesn't work (replaces nothing) - range.Replace(";Color=12342", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); And this line of code inserts a space at the end of each cell, which I don't want - range.Replace(";Color=12342", " ", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, true); Why does the first line of code above not work? Thanks, Barry. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
| Find and replace part of a text string | Excel Discussion (Misc queries) | |||
| Long character string - need to remove part of it | Excel Programming | |||
| remove last part of string | Excel Programming | |||
| Remove part of string | Excel Programming | |||
Hybrid Mode
