How to Trim a string

[sugaprasad]

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.

[nilem]

for example

Please Login or Register  to view this content.

[kvsrinivasamurthy]

If your Text is in A1 cell use this formula

Please Login or Register  to view this content.

[kvsrinivasamurthy]

If your Text is in A1 cell use this formula

Please Login or Register  to view this content.

[sugaprasad]

If your Text is in A1 cell use this formula

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

[sugaprasad]

for example

Please Login or Register  to view this content.

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

[kvsrinivasamurthy]

In your code Change this line

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

[sugaprasad]

In your code Change this line

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

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

[kvsrinivasamurthy]

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

[sugaprasad]

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

No. I just noted that in most of the cases it will be in the begining. But there are chances it might come in the middle as well.

[kvsrinivasamurthy]

After this line

Please Login or Register  to view this content.

Pl add this code

Please Login or Register  to view this content.

Before this line

Please Login or Register  to view this content.

This will extract words containing % or $.

[sugaprasad]

After this line

Please Login or Register  to view this content.

Pl add this code

Please Login or Register  to view this content.

Before this line

Please Login or Register  to view this content.

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.

[sugaprasad]

After this line

Please Login or Register  to view this content.

Pl add this code

Please Login or Register  to view this content.

Before this line

Please Login or Register  to view this content.

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.