How to Trim a string

Hi Everyone,

I need a trim function that will do the following thing.

For example consider i'm assigning this line "Value - $10 for pickup" to a variable. I need to get that $10 alone from that line and assign to another variable. this line varies from time to time. sometimes it will be like "After Pickup $5,000 for drop to home". So what I need is i need to extract the amount alone that is $10, $5,000 from any line and place it in a variable. How to handle this. Any Idea. Thanks in advance.

Originally Posted by nilem

for example

Sub ert()
Dim s As String, txt As String
s = ActiveCell.Value
With CreateObject("VBScript.RegExp")
    .Pattern = "\$[0-9]+,?[0-9]+" ' .Pattern = "\$\d+,?\d+"
    If .Test(s) Then
        txt = .Execute(s)(0)
    End If
    MsgBox txt
End With
End Sub

Thank You for the reply. I posted my concern in the previous post.Please let me know if you need any additional inputs

If your Text is in A1 cell use this formula

=MID(A1,FIND("$",A1),FIND(" ",A1,FIND("$",A1))-FIND("$",A1))

If your Text is in A1 cell use this formula

=MID(A1,FIND("$",A1),FIND(" ",A1,FIND("$",A1))-FIND("$",A1))

Originally Posted by kvsrinivasamurthy

If your Text is in A1 cell use this formula

=MID(A1,FIND("$",A1),FIND(" ",A1,FIND("$",A1))-FIND("$",A1))

Hi. Thank you for the reply. I would like to let you know that i'm not taking the text from a cell in excel sheet. I'm taking the text from a word document and assigning it to a variable and then passing that variable to an excel cell. so inbetween i want a trim function to happen. I'm attaching the code for your reference. This should throw some light.

Sub FindAndCopyNext()

    Dim TextToFind As String, TheContent As String, Trm As String, wdApp As Object
    Dim rng As Word.range
    Dim objWord As Object
    Dim oExcel As Object
    Dim oSheet As Object
    Dim oBook As Object
    Dim sExcelPath As String
    Dim range As Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
    objWord.Documents.Open "C:\Documents and Settings\Prasad\Desktop\ABC\Sample.doc"

    TextToFind = "Lowest-Cost Option" 'the text you're looking for to
                          ' locate the other content
                          
                          

    Set rng = ActiveDocument.Content
    rng.Find.Execute FindText:=TextToFind, Forward:=True

    If rng.Find.Found Then
        If rng.Information(wdWithInTable) Then
          TheContent = rng.Cells(1).Next.range.Text      'move right on row
          'TheContent = rng.Cells(1).Previous.Range.Text 'move left on row
          MsgBox "Found content '" & TheContent & "'"
        End If
    Else
        MsgBox "Text '" & TextToFind & "' was not found!"
    End If


Set oSheet = ActiveWorkbook.Worksheets(2)
Set range = oSheet.UsedRange


'Need to trim 'TheContent' here before passing to excel cell. Lets say 'TheContent' has a value "After Pickup $5,000 for drop to home". I need to pass only $5,000

range.Cells(2, 9) = TheContent

End Sub

In your code Change this line

range.Cells(2, 9) = TheContent

as

range.Cells(2, 9) = =Mid(TheContent, InStr(1, TheContent, "$"), InStr(InStr(1, TheContent, "$"), TheContent, " ") - InStr(1, TheContent, "$"))

Originally Posted by kvsrinivasamurthy

In your code Change this line

range.Cells(2, 9) = TheContent

as

range.Cells(2, 9) = =Mid(TheContent, InStr(1, TheContent, "$"), InStr(InStr(1, TheContent, "$"), TheContent, " ") - InStr(1, TheContent, "$"))

That's awesome piece of code. works like charm.
I have one more thing to say you.
sometimes there will be % instead of $. For example I will have a line like "30% commission, after drop". So in this case what i'm supposed to do. I need to seperate that 30% alone from the whole sentence. Thanks in advance

Will the figure like 30% appear always in the begining.

Originally Posted by kvsrinivasamurthy

After this line

'Need to trim 'TheContent' here before passing to excel cell. Lets say 'TheContent' has a value "After Pickup $5,000 for drop to home". I need to pass only $5,000

Pl add this code

Dim T As Integer
Dim M As Variant

M = Split(TheContent, " ")

For T = 0 To UBound(M)
If InStr(1, M(T), "%") > 0 or InStr(1, M(T), "$") > 0 Then
TheContent = M(T)
Exit For
End If
Next T

Before this line

range.Cells(2, 9) = TheContent

This will extract words containing % or $.

Wow..that's simply awesome. Its working great. Thank you so much for your help. I'm going to mark this thread as solved.

Originally Posted by kvsrinivasamurthy

After this line

'Need to trim 'TheContent' here before passing to excel cell. Lets say 'TheContent' has a value "After Pickup $5,000 for drop to home". I need to pass only $5,000

Pl add this code

Dim T As Integer
Dim M As Variant

M = Split(TheContent, " ")

For T = 0 To UBound(M)
If InStr(1, M(T), "%") > 0 or InStr(1, M(T), "$") > 0 Then
TheContent = M(T)
Exit For
End If
Next T

Before this line

range.Cells(2, 9) = TheContent

This will extract words containing % or $.

This Code is working great. But i'm facing one problem here. Suppose if there is no space between $ value and the next word its not triming the $value alone.

For example consider there is a line "After Pickup $5,000for drop to home"

In the above sentence there is no space between $5000 and for. So $5000for is getting assigned to the TheContent variable instead of $5000.
I would like to assign only the number value to TheContent variable. Can you please help me for one more time.