Delete everything after the second space in each cell

[DBrian]

I have a column containing an address in each row. I need something in VBA that will delete everything after the second space in each address, including the second space. The closest thing I could find was this formula:

=LEFT(A1,Find(" ",A1)-1)

This deletes everything after the first space in a cell but I need something to include in a VBA routine. Any suggestions are greatly appreciated.

 

[mikerickson]

The formula =TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ",255),2), 255)) will do what you want.
If I were to write a generealized VBA routine, I'd have the routine put that formula in cells, then convert to constants.

With Selection.Offset(0,1)
    .FormulaR1C1 = "=TRIM(LEFT(SUBSTITUTE(A1, "" "", REPT("" "",255),2), 255))"
    .Value = .Value
End With

 

[Rick Rothstein]

I have a column containing an address in each row. I need something in VBA that will delete everything after the second space in each address, including the second space. The closest thing I could find was this formula:

=LEFT(A1,Find(" ",A1)-1)

This deletes everything after the first space in a cell but I need something to include in a VBA routine. Any suggestions are greatly appreciated.

So you are looking for VB code to return everything before the second space in cell A1, correct?

CellVal = Range("A1").Value
TextBeforeSecondSpace = Left(CellVal, InStr(InStr(CellVal, " ") + 1, CellVal, " ") - 1)

 

[Rick Rothstein]

The formula =TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ",255),2), 255)) will do what you want.
If I were to write a generealized VBA routine, I'd have the routine put that formula in cells, then convert to constants.

With Selection.Offset(0,1)
    .FormulaR1C1 = "=TRIM(LEFT(SUBSTITUTE(A1, "" "", REPT("" "",255),2), 255))"
    .Value = .Value
End With

You meant to use the .Formula property instead of the .FormulaR1C1 property, correct?

Another way which will also work...

With Selection.Offset(0, 1)
    .Formula = "=LEFT(A1,FIND("" "",A1,FIND("" "",A1)+1)-1)"
    .Value = .Value
End With

 

[mikerickson]

I intented to use the FormulaR1C1 property, but forgot to convert the formula.

With Selection.Offset(0,1)
    .FormulaR1C1 = "=TRIM(LEFT(SUBSTITUTE(RC[-1], "" "", REPT("" "",255),2), 255))"
    .Value = .Value
End With

 

[DBrian]

Thanks for your help.

 

[DBrian]

Thanks. That works great.

 

[Rick Rothstein]

I intented to use the FormulaR1C1 property, but forgot to convert the formula.

With Selection.Offset(0,1)
    .FormulaR1C1 = "=TRIM(LEFT(SUBSTITUTE(RC[-1], "" "", REPT("" "",255),2), 255))"
    .Value = .Value
End With

Just curious as to why you used the FormulaR1C2, which required you to convert the formula you had to R1C1 notation, when assigning the formula you had to the Formula property would also work. Or am I missing some underlying "something" here that make one work better than the other?

 

[mikerickson]

The formula was put in Selection.Offset(0,1). Using A1 with .Formula would have converted column A rather than Selection.

But in general, for some reason, in VBA, my thought process about relative addressing works better with R1C1 notation. The back of my mind thinks that Range("C1:C3").Formula = "=A1+1" will put "=A1+1" in C3. Yes, I know better, but using R1C1 notation calms the back of my mind.

 

[Rick Rothstein]

The formula was put in Selection.Offset(0,1). Using A1 with .Formula would have converted column A rather than Selection.

That is a good point (which I completely overlooked).

But in general, for some reason, in VBA, my thought process about relative addressing works better with R1C1 notation. The back of my mind thinks that Range("C1:C3").Formula = "=A1+1" will put "=A1+1" in C3. Yes, I know better, but using R1C1 notation calms the back of my mind.

Plus, as you noted above, it guarantees that the point you made above would not have to be watched out for. Thanks for responding.