I need help,
I use excel I have a problem want to ask, written macro:
want to delete a last character example:
"Danny Hein T" I want to delete the last character "T" otherwise leave there.

THanks
D

D

__________________
D

Hi

You could do something like this:

A = Cells(i, 1).Text 'Your cell Containing the "Danny Hein T" Value

c = Len(A) 'Get the lenght of the text
B = Mid(A, c, 1) 'This will give you the last value
If B = "T" Then 'If the last value is "T" you could do whatever
    Text = Replace(A, "T", "") 'This is a bit dodgy as it will replace all T's in you string
    Range("A" & "1").Select
    ActiveCell.FormulaR1C1 = Text

End If

Another way you could do it if all the strings are like your example is to split the string at every space: e.g

A = "Danny Hein T"
X = split(A," ")
Name = X(0)
Surname = X(1)
Last = X(2)
If Last = "T" Then
Range("A" & "1").Select
    ActiveCell.FormulaR1C1 = Name & " " & Surname
Else
    Range("A" & "1").Select
    ActiveCell.FormulaR1C1 = Name & " " & Surname & " " & Last
End if


There are probably other ways of doing it to....hope this helps.

Regards
Marnus

Alternatively...

ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 1)

This takes the length of the content and reduces it by 1 and uses that value to extract the specified number of characters.

cheers

Matt

One more question; if other cells in that column does not have a character in the last string like: "Jimmy Lee" Let me give example:
"Hein Bruce T"
"Jimmy Lee"
"Gant Bush L"
So how do we write Macro with delete last character like: "Hein Bruce T, and Gant Bush L"

Thank your help
D

D

Hi
Try this one

If Mid(ActiveCell, Len(ActiveCell) - 1, 1) = " " Then
    ActiveCell = Left(ActiveCell, Len(ActiveCell) - 2)
End If

-vemaju

Hi vemaju, or other helper,
Vemaju your funtion works, how do we write the FOR statement to search all the cells (1 column)?
Thanks
D

D

Hi,

You can use For Each Next Loop for any selection

Dim Cell As Range

For Each Cell in Selection
    If Mid(Cell, Len(Cell) - 1, 1) = " " Then
        Cell= Left(Cell, Len(Cell) - 2)
    End If
Next


-vemaju

Thank you very much Vemaju,
I read the Mid Statement you used, I am still confused that. So I have another question if we want to delete a character in the middle string like: "Hein T Bruce" what is statement we use delete character "T".
Thanks again your support
D

D

Hi Vemaju,
I asked you request the delete middle a character in string, can we delete a character, but if the string like "Hein & Lee" I don't want to delete that "&" can we do that?
Sincerely your
D

D

Hi,

Quickly build function

Function DelStr(Word As String) As String
Dim b As Byte
Dim Found As Boolean

    For b = 1 To Len(Word)
        If Mid(Word, b, 1) = " " And Mid(Word, b + 2, 1) = " " Then
            If Mid(Word, b + 1, 1) <> "&" Then
                Found = True
                Exit For
            End If
        End If
    Next
    If Found = True Then
        DelStr = Left(Word, b) & Right(Word, Len(Word) - b - 2)
    Else
        DelStr = Word
    End If

End Function

-vemaju