Removing Everything behing 3 Spaces

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peter T]

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