Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Everything behing 3 Spaces
Hello,
I hope somebody can help me with this. I have a colum and in that colum are differect cells with text. Example OPTERON 250 2.4GHZ FSB1000 CHIP Like you see there are a fex spaces and the nthe word CHIP. I'm looking for a code finding that is scanning the row (In this case Row.C) Finding the cells containing the three spaces and removing everything followed after the spaces. The final result will be OPTERON 250 2.4GHZ FSB1000 Is there anyway to fix this? Thanks already for all your help! Rony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Everything behing 3 Spaces
I hope somebody can help me with this.
I have a colum and in that colum are differect cells with text. Example OPTERON 250 2.4GHZ FSB1000 CHIP Like you see there are a fex spaces and the nthe word CHIP. I'm looking for a code finding that is scanning the row (In this case Row.C) Finding the cells containing the three spaces and removing everything followed after the spaces. The final result will be OPTERON 250 2.4GHZ FSB1000 Is there anyway to fix this? Either test for the 3 spaces first, like this... If InStr(YourString, " ") Then <TheCell'sValue = Left$(<TheCell'sValue, InStr(YourString, " ") - 1) End If or, if there aren't thousands of cell's involved, just run each cell through this... <TheCell'sValue = Left$(YourString, InStr(<TheCell'sValue & " ", " ") - 1) where you would, of course, put the actual reference to the cell from your loop in place of my place-holder (<TheCellValue). Rick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Everything behing 3 Spaces
One way -
Sub test() Rem3space ActiveSheet.Columns(3) End Sub Sub Rem3space(rng As Range) Dim pos As Long Dim s As String Dim cel As Range, rngTextCells As Range On Error Resume Next Set rngTextCells = rng.SpecialCells(xlCellTypeConstants, 2) On Error GoTo 0 If rngTextCells Is Nothing Then Exit Sub For Each cel In rngTextCells s = cel.Value pos = InStr(1, s, " ") If pos Then cel = Left(s, pos - 1) End If Next End Sub I assume when you say "Row C" you mean Column-C ! Regards, Peter T "Rony" wrote in message ... Hello, I hope somebody can help me with this. I have a colum and in that colum are differect cells with text. Example OPTERON 250 2.4GHZ FSB1000 CHIP Like you see there are a fex spaces and the nthe word CHIP. I'm looking for a code finding that is scanning the row (In this case Row.C) Finding the cells containing the three spaces and removing everything followed after the spaces. The final result will be OPTERON 250 2.4GHZ FSB1000 Is there anyway to fix this? Thanks already for all your help! Rony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing extra spaces | Excel Discussion (Misc queries) | |||
Removing Spaces | Excel Discussion (Misc queries) | |||
removing all spaces | Excel Discussion (Misc queries) | |||
Removing spaces from value using VBA | Excel Programming | |||
removing spaces | Excel Discussion (Misc queries) |