Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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

Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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

Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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

Reply With Quote
  #8 (permalink)  
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
Reply With Quote
  #9 (permalink)  
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
Reply With Quote
  #10 (permalink)  
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:40 AM.


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