Trim Between Characters

5 pts.
Tags:
Strings
SUBSTR
TRIM function
How do I Trim the text after a spcific character? (ie. www.website.com#http://www.website.com#) Trimed to www.website.com

Answer Wiki

Thanks. We'll let you know when a new response is added.

I am giving you a function having two arguments. 1. is the text to be trimmed, 2. is the specific character.
I think this will solve your problem.

Public Function TrimedText(TextToTrim As String, SpecificCharacter As String) As String
TrimedText = ""
For i = 1 To VBA.Len(TextToTrim)
If VBA.Mid(TextToTrim, i, VBA.Len(SpecificCharacter)) <> SpecificCharacter Then
TrimedText = TrimedText & VBA.Mid(TextToTrim, i, 1)
Else
Exit For
End If
Next
End Function

Try this in excel.
Start Excel, Tools Menu, Macro, Visual Basic Editor.
In Visual Basic Editor, Insert Menu, Module.
In Module, Copy and Paste above code. Save and close Visual Basic Editor.
In Excel, in cell ‘A1’ write your text string i. e. www.website.com#http://www.website.com#), in cell ‘A2’ write the function =TrimedText(A1,”#”).

You will get the solution.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ToddN2000
    Sounds like a home work question.
    What language are you looking to do this in?
    Post your code and let us see what you have tried.
    Are you getting errors or is it just not working?
    42,790 pointsBadges:
    report
  • TheRealRaven
    Impossible to answer without knowing what language is used. Give enough details about the programming environment for us to suggest answers. Include the platform/OS, as well as the DBMS if this is about SQL.
    18,995 pointsBadges:
    report
  • CharlieBrowne

    I assume you are using SQL.

    Try the %REPLACE or %TRIM functions

    45,270 pointsBadges:
    report
  • ToddN2000
    When looking to do the trim and replace you need to make sure the string is unique enough that you do not effect the other string data.

    42,790 pointsBadges:
    report
  • TheRealRaven
    I'd most likely use whatever scan, position or locate type of function would return the character index of the target character (e.g., "#"). Then use either a left or a substring function for a length of (position - 1).
    18,995 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following