Debug problem with mid statement

[Smooth2o]

Here's the code:

Please Login or Register  to view this content.

Here is the error:
Macro error.jpg

Here is the data: (the information is bogus and not that of a real customer)
Data example.jpg

I am trying to extract the email address from a long string with leading blanks? in column B. The email address is then written to column B one row below the data array. About 3000 records.

[Norie]

The problem is that either StartEmailChar or LengthofName are invalid.

That could be happening because the InStrs aren't finding what they are looking for, or are finding it in the wrong place.

Hard to tell without any data.

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Smooth2o]

I did post a picture of the data in column B, but here is a copy of the data below. I believe that the data has one leading blank:
Data starts here=>
First Name: Robert W

Last Name: Beaver
Address 1: 4540 Lenard Lane

Address 2:

City: Eagle

State: MN

Zip: 55134-2413

E-mail: [email protected]



I would like more information on the following:



Comments:

Your<= Data ends here.

[Norie]

I know you attached a picture but you can't run code on a picture.

I'll take a look at the data you posted but it would be better to have it in a workshee context - there could be hidden characters which wouldn't be copied across.

Anyway, in the mean time I did notice this:

Please Login or Register  to view this content.

Do you mean to search for an empty string?

Is it perhaps meant to be a space, " "?

[Norie]

Just realised the problem could also be caused by blank cells.

So you should add a check for the,

Please Login or Register  to view this content.

[Smooth2o]

I did post a picture of the data in column B, but here is a copy of the data below. I believe that the data has one leading blank:
Data starts here=>
First Name: Robert W

Last Name: Beaver
Address 1: 4540 Lenard Lane

Address 2:

City: Eagle

State: MN

Zip: 55134-2413

E-mail: [email protected]



I would like more information on the following:



Comments:

Your<= Data ends here.

[Smooth2o]

Attached is new code and data example.

You are right, I think it should be " " for a blank (if it is):
Also, I added a 8 instead of a 7 at the end of the StartEmailChar as I noticed that there was a blank between the "E-Mail:" and the start of the email name:

Please Login or Register  to view this content.

Test file for Consumer signups.xlsm

[Smooth2o]

OK, so here is the new code with the null check:

Please Login or Register  to view this content.

Now, I get through the debug routine fine, but no email data is stored below the matrix.

[Smooth2o]

OK, so here is the new code with the null check:

Please Login or Register  to view this content.

Now, I get through the debug routine fine, but no email data is stored below the matrix.

[Norie]

You are looking in the wrong column.

Also the data is separated by Chr(10) not " ".

Please Login or Register  to view this content.

Here's a different way to get the email.

Please Login or Register  to view this content.

[Smooth2o]

Hi Norie,

OK, thanks for that programming tip. I will look up the split function.

When I look at the data, I find that the email address is as follows:
E-mail: [email protected] I would l
32 32 32 32 69 45 109 97 105 108 58 32 114 99 99 49 49 53 52 57 64 109 115 110 46 99 111 109 32 32 32 32 73 32 119 111 117 108 100 32 108

So, there are spaces before the email address and spaces after. I don't find any Chr(10)'s. There is one space after "E-Mail". There are also spaces (4) after the email address.

So, I rewrote the code as follows:

Please Login or Register  to view this content.

And, it WORKS!, sort of.

In the answer field, I get the email address surrounded by quotes, as in:
"Theemailaddress.com"

So, I added the code to remove the " at the beginning and the " at the end.
But, I still get the quotes at the ends of the field.

Here is the new code:

Please Login or Register  to view this content.

[Smooth2o]

So, why are the quotes occurring. I expected just a plain string.

Also, how do I get rid of them?

[zbor]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

I've edited first post now for you.

[Smooth2o]

OK, Thanks, but I didn't know I could edit a post that was already posted so I didn't bother to edit it.

Explain how to edit a post already posted, pls.

[Smooth2o]

OK, Thanks, but I didn't know I could edit a post that was already posted so I didn't bother to edit it.

Explain how to edit a post already posted, pls.

Aha! I see the edit button now after I posted....

[Norie]

Both sets of code worked on your data, extracting the email addresses with no spaces.

There are Chr(10)s in the original data - that's why it's displayed over several lines.

In fact there's 13 in B2.

[Smooth2o]

Norie,

Thanks for all the help, I'm learning fast.

OK, I must be using the wrong tool.

Here is the tool I am using to convert to Decimal:

http://easycalculation.com/ascii-hex.php

When I use this tool, I find no 10's or 13's in the data.

When I paste the result of the macro from B12 into this post, I get this:

"[email protected]
"

NOTE: You don't see the " signs in the Excel cell.

1. So there are some strange characters in the result. How do I find out what they are, and get rid of them?

2. Is there a better tool to use?

Rick

[Norie]

Rick

I didn't use any tools, I just used a formula in I2:

Please Login or Register  to view this content.

I copied that down to get all the ASCII codes.

Bit basic I know but it works.

[FDibbins]

at the bottom right of each post there are "edit" "reply" and "reply with quotes" buttons, use "edit" on the particular post you want to edit

[Smooth2o]

Norie,

Thanks so much for your help. Great experience. Very grateful.

Rick