find and delete text, find a 10-digit number and put it in a textbox

Hi all,

I have been trying to find the answer for this question for a long
time. I have a userform that contains textbox2 and textbox 3.

A user can copy and paste (yes I did find how to add the menu items
for copy and paste in a userform) a certain line of text in textbox2.
This will look like this:

texttexttexttext item number: 9876543210

I want a macro that finds the text "item number: " and deletes it from
textbox2, something like:

If InStr(1, TextBox2.Value, "Item number: ", vbTextCompare) > 0 Then
TextBox2.Value = ... (TextBox2.Value minus "Item number: ")

Furthermore, I want it to find the item number, 9876543210 which can
be any number but will always have 10 digits, after which this item
number is put in textbox3. To make it even more complicated, the
string texttexttexttext may also contain one or two numbers but never
10 digits in a row.

What may help is that the text string as I just gave will always be in
that format, so the item number will always be at the end of the
textbox string.

I hope somebody can help me with this, I am not that experienced in
vba yet...

Thanks in advance,
Paul

If InStr(1, TextBox2.Value, "Item number: ", vbTextCompare) > 0 Then
TextBox2.Value =Application.Substitute(Textbox2.Text,"Item number","")
end if
Textbox3.Text = right(Textbox2.Text,10)

--
Regards,
Tom Ogilvy



"Paul" > wrote in message
m...
> Hi all,
>
> I have been trying to find the answer for this question for a long
> time. I have a userform that contains textbox2 and textbox 3.
>
> A user can copy and paste (yes I did find how to add the menu items
> for copy and paste in a userform) a certain line of text in textbox2.
> This will look like this:
>
> texttexttexttext item number: 9876543210
>
> I want a macro that finds the text "item number: " and deletes it from
> textbox2, something like:
>
> If InStr(1, TextBox2.Value, "Item number: ", vbTextCompare) > 0 Then
> TextBox2.Value = ... (TextBox2.Value minus "Item number: ")
>
> Furthermore, I want it to find the item number, 9876543210 which can
> be any number but will always have 10 digits, after which this item
> number is put in textbox3. To make it even more complicated, the
> string texttexttexttext may also contain one or two numbers but never
> 10 digits in a row.
>
> What may help is that the text string as I just gave will always be in
> that format, so the item number will always be at the end of the
> textbox string.
>
> I hope somebody can help me with this, I am not that experienced in
> vba yet...
>
> Thanks in advance,
> Paul

Thank you Tom, I did not know the "substitute" command there.

For the second problem I was hoping for a solution that could detect
whether the string contains 10 numbers in a row. My problem is that I
do not know whether or not the user copied the complete line including
the item number or just the text string without the item number.

Something like...
if(textbox2.value="####(numbers)" then textbox3.value =
"#####(numbers)"

And like I said, the string text may also contain one or two numbers
but I am looking for a string of 10 consecutive numbers.

I don't know if this is possible at all but I do appreciate your help!
Paul




"Tom Ogilvy" > wrote in message >...
> If InStr(1, TextBox2.Value, "Item number: ", vbTextCompare) > 0 Then
> TextBox2.Value =Application.Substitute(Textbox2.Text,"Item number","")
> end if
> Textbox3.Text = right(Textbox2.Text,10)
>
> --
> Regards,
> Tom Ogilvy
>

It actually didn't take too long to find a solution myself. Sorry!

My own solution:

If IsNumeric(Right(TextBox2.Text, 11)) Then
TextBox3.Value = Right(TextBox2.Text, 11)
TextBox2.Value = Application.Substitute(TextBox2.Text,
Right(TextBox2.Text, 11), "")
End If