Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 2nd, 2005, 06:33 PM
Authorized User
 
Join Date: Nov 2005
Location: San Diego, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete last a character in string

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
 
Old November 3rd, 2005, 01:45 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Johannesburg, , South Africa.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

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
 
Old November 3rd, 2005, 04:52 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 3rd, 2005, 02:00 PM
Authorized User
 
Join Date: Nov 2005
Location: San Diego, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 6th, 2005, 12:43 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
Try this one

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

-vemaju

 
Old November 7th, 2005, 07:54 PM
Authorized User
 
Join Date: Nov 2005
Location: San Diego, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 8th, 2005, 03:11 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 8th, 2005, 02:23 PM
Authorized User
 
Join Date: Nov 2005
Location: San Diego, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 8th, 2005, 02:35 PM
Authorized User
 
Join Date: Nov 2005
Location: San Diego, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 9th, 2005, 04:46 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 10 Cannot Delete Character lucius BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 3 February 5th, 2008 12:45 AM
Cutting a character from a string arnabghosh Classic ASP Basics 2 August 18th, 2005 07:44 AM
Replacing a character from string itHighway Classic ASP Basics 5 March 14th, 2005 11:15 PM
Search for Character in String Kaynor09 Excel VBA 2 April 24th, 2004 11:13 AM
How do I get rid of the last character in a string Lucy Classic ASP Professional 3 September 30th, 2003 05:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.