Как удалить последний символ в excel vba

Добрый вечер! Наверное я с очень простым вопросом, но буду искренне признательна за ответ:
на userform размещены несколько checkbox`ов, при кликании на которые в переменную последовательно заносятся соответствующие текстовые значения. На событие click каждого checkbox`а стоит вызов процедуры (см. код). С помощью процедуры между значениями ставится запятая. Беда в том, что в результате запятая ставится и после завершающего текстового значения. Подскажите, пожалуйста, как можно в итоговой переменной (S) программно удалить последний символ (запятую)?

Sub Result()
Const p = «, »
Dim S$
S = «на основании: »
If Me.cb_Sobstv.Value = True Then S = S & «права собственности» & p
If Me.сb_Arenda.Value = True Then S = S & «договора аренды» & p
If Me.cb_Xran.Value = True Then S = S & «договора ответственного хранения» & p
If Me.cb_Bank.Value = True Then S = S & «договора залога» & p
Vlad.Value = S
End Sub

Источник

0 / 0 / 0

Регистрация: 02.04.2013

Сообщений: 8

Excel

Как удалить последний символ в строке?

01.11.2020, 19:59. Показов 4352. Ответов 4

Нужно реверсировать строку, появляется лишняя запятая в конце строки. Как убрать последнюю запятую?

Visual Basic

Sub ReverseWord()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Sigh As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Sigh = Application.InputBox("Symbol interval", xTitleId, ",", Type:=2)
For Each Rng In WorkRng
    strList = VBA.Split(Rng.Value, Sigh)
    xOut = ""
    For i = UBound(strList) To 0 Step -1
        xOut = xOut & strList(i) & Sigh
    Next
    Rng.Value = xOut
Next
End Sub

Допустим строка
aa1, bb1, cc1, dd1, ee1, ff1
Результат
ff1, ee1, dd1, cc1, bb1,aa1,
А надо
ff1, ee1, dd1, cc1, bb1,aa1

Источник

You can use the following basic syntax to remove the last character from a string using VBA:

Sub RemoveLastChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 1)
    Next i
    
End Sub

This particular example removes the last character from each string in the range A2:A11 and outputs the results in the range B2:B11.

The following example shows how to use this syntax in practice.

Example: Using VBA to Remove Last Character from Strings

Suppose we have the following list of basketball team names in Excel:

Suppose we would like to remove the last character from each team name.

We can create the following macro to do so:

Sub RemoveLastChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 1)
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B displays each of the strings in column A with the last character removed.

If you would instead like to remove the last n characters from a string, simply change the 1 in the Left method to a different number.

For example, we can create the following macro to remove the last 2 characters from a string:

Sub RemoveLastTwoChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 2)
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B displays each of the strings in column A with the last two characters removed.

Note: You can find the complete documentation for the VBA Left method here.

Additional Resources

The following tutorials explain how to perform other common tasks using VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text

Источник

in this example below I want to remove the «B» using VBA. Thus, I download these values which spam from B5:F50 and all in the end have «B». My question is twofold. A) Is there a way to delete the «B» using VBA and replace these values in the same cells? B) Or have to be in new cells?

77.65B 86.73B 92.97B 84.7B 89.4B

asked Aug 16, 2017 at 13:56

The code would be like this

Sub test()
    Dim rngDB As Range, vDB
    Dim s As String, i As Integer, j As Integer

    Set rngDB = Range("b5:f50")
    vDB = rngDB
    r = UBound(vDB, 1)
    c = UBound(vDB, 2)
    For i = 1 To r
        For j = 1 To c
            s = vDB(i, j)
            s = Left(s, Len(s) - 1)
            vDB(i, j) = s
        Next j
    Next i
    rngDB = vDB

End Sub

Sub test2()
    Dim rngDB As Range        
    Set rngDB = Range("b5:f50")
    rngDB.Replace "B", "", lookat:=xlPart

End Sub

answered Aug 16, 2017 at 14:11

The easiest (shortest) way I can see to do this is with a Search/Replace, but this would be on the assumption that all cells end in B and a B doesn’t legitimately occur elsewhere in the cells.

Sheets("YourSheetNameHere").Range("B5:F50").Replace What:="B", Replacement:="", LookAt:=xlPart

Alternatively, you could loop through each cell and RIGHT/LEN etc. but why bother?

answered Aug 16, 2017 at 14:13

CLRCLR

10.7k1 gold badge10 silver badges26 bronze badges

Источник

16th
Окт

Макрос удаляющий во всех ячейках столбца последний символ

Posted by Chas under Basic, Пост-обзор

IgorGO
перед запуском макроса активной должна быть любая ячейка редактируемого столбца

sub DelLastChar

  dim r as long, v as string

  for r = 1 to cells(rows.count, activecell.column).end(xlup).row

    v = cstr(cells(r, activecell.column).value)

    if v <> "" then cells(r, activecell.column).value = left(v, len(v)-1)

  next

end sub

kuklp
Вариант без цикла:

Sub www()

Dim r$

r = Range(Cells(1, ActiveCell.Column), Cells(65536, ActiveCell.Column).End(xlUp)).Address

Range(r) = Evaluate("IF(" & r & "="""","""",LEFT(" & r & ",LEN(" & r & ")-1))")

End Sub

vikttur
Вариант без макроса

=ПСТР(A1;1;ДЛСТР(A1)-1)

=ЛЕВСИМВ(A1;ДЛСТР(A1)-1)

тема на форуме

Как удалить последний символ в строке?

Example: Using VBA to Remove Last Character from Strings

Additional Resources

Макрос удаляющий во всех ячейках столбца последний символ

Похожие статьи

А вот еще интересные статьи: