replacing the last space in a variable with an &...

[GS7CLW]

I have a variable that may consist of 1 to 6 words:

Variable name = NewWords

a sample sting may be:

PHYS

PHYS ENLIST

TEST PHYS ENLIST

I am trying to figure out how to replace the last space within the variable with 2 spaces and the ampersand, so that:

TEST PHYS ENLIST

would end up being:

TEST PHYS & ENLIST

I am doing this within a macro.

Hope everyone had a great weekend!!!
cliff

 

[njimack]

=if(len(a1)=len(substitute(a1," ","")),a1,substitute(a1," "," & ",len(a1)-len(substitute(a1," ",""))))

EDIT: Oops, just noticed you want a VBA solution...

 

[Ruddles]

InStrRev returns the last occurrence of a character in a string, so if you have "TEST PHYS ENLIST" in NewWords:-

Dim iSpace As Integer
iSpace = InStrRev(NewWords, Space(1))
If iSpace > 0 Then NewWords = Left(NewWords, iSpace) & "&" & Mid(NewWords, iSpace)

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG28Feb10
[COLOR="Navy"]Dim[/COLOR] MyStg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
'[COLOR="Green"][B]MyStg = "PHYS"[/B][/COLOR]
MyStg = "TEST PHYS ENLIST"
num = InStrRev(MyStg, " ")
MyStg = IIf(num > 0, Left(MyStg, num) & "&" & Right(MyStg, Len(MyStg) - num + 1), MyStg)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[njimack]

Will perform the substitution on all selected cells...

Sub test()
Dim r As Range
Dim Count

For Each r In Selection
Count = Len(r.Value) - Len(WorksheetFunction.Substitute(r.Value, " ", ""))

If Count <> 0 Then r.Value = WorksheetFunction.Substitute(r.Value, " ", " & ", Count)

Next r


End Sub

 

[GS7CLW]

InStrRev returns the last occurrence of a character in a string, so if you have "TEST PHYS ENLIST" in NewWords:-

Dim iSpace As Integer
iSpace = InStrRev(NewWords, Space(1))
If iSpace > 0 Then NewWords = Left(NewWords, iSpace) & "&" & Mid(NewWords, iSpace)

Mr Ruddles,

I tried yours and it puts the ampersand at the very end of the strings.
but I do appreciate the help.

cliff

 

[GS7CLW]

basically what I am trying to do is fill in the blank:

If InStr(c.Value, " ") > 0 Then ' test for a space
NewWords =                      ' if there is a space; replace the LAST space with an &
End If

a sample line (NewWords) could be:
PHYS ENLIST
LA
TEST PHYS ENLIST

 

[GS7CLW]

SOLVED: replacing the last space in a variable with an &...

Mr Ruddles:

I ran:

NewWords = Trim(NewWords)

just before your code and everything works perfectly now.

THANKS!!!!
cliff

 

[Ruddles]

Are you building NewWords one word at a time? Maybe the trailing space is appearing at that stage.

 

[GS7CLW]

basically yes:

Sub CleanUpCommentColumn()
'
' Clean Up the Comment Column Macro
' AUTHORIZED WORD LIST
' LA
' CONSULT
' INSPECT
' TEST
' PHYS
' ENLIST
' DEP IN
' TAPAS
' INTERVIEW
' SEC INT
Application.ScreenUpdating = False ' turn OFF the screen updating
'---dim the variables
Dim NewWords As String
Dim c As Range
For Each c In Range("L1:L200")
If InStr(c.Value, "LATE") > 0 Then
NewWords = "LA "
End If
If InStr(c.Value, "*PS*") > 0 Then
NewWords = NewWords & "PS "
End If
If InStr(c.Value, "PRIOR SERVICE") > 0 Then
NewWords = NewWords & "PS "
End If
If InStr(c.Value, "CONSULT") > 0 Then
NewWords = NewWords & "CONSULT "
End If
If InStr(c.Value, "TAPAS") > 0 Then
NewWords = NewWords & "TAPAS "
End If
If InStr(c.Value, "INSPECT") > 0 Then
NewWords = NewWords & "INSPECT "
End If
If InStr(c.Value, "ASVAB") > 0 Then
NewWords = NewWords & "TEST "
End If
If InStr(c.Value, "TEST") > 0 Then
NewWords = NewWords & "TEST "
End If
If InStr(c.Value, "PHYS") > 0 Then
NewWords = NewWords & "PHYS "
End If
If InStr(c.Value, "ENLIST") > 0 Then
NewWords = NewWords & "ENLIST "
End If
If InStr(c.Value, "DEP") > 0 Then
NewWords = NewWords & "DEP-IN "
End If
If InStr(c.Value, "INTERVIEW") > 0 Then
NewWords = NewWords & "SEC-INT "
End If
NewWords = Trim(NewWords)
Dim iSpace As Integer
iSpace = InStrRev(NewWords, Space(1))
If iSpace > 0 Then NewWords = Left(NewWords, iSpace) & "&" & Mid(NewWords, iSpace)
c.Value = NewWords
NewWords = ""
Next c
Columns("L:L").EntireColumn.AutoFit
Application.ScreenUpdating = True ' turn ON the screen updating
End Sub

It works perfectly!!!!!!!!!