Can't convert text to numbers

[HuskerBronco]

I copy/pasted a college football schedule from a webpage.
I need to be able to paste this schedule straight into my spreadsheet.
I can't do this because all of the teams that are currently ranked have their rankings right next to their team names.
(Ex: 1 Alabama)

I realized immediately that these numbers must be in text form, rather than number form.
I've tried every way I can find to make a TEXT to NUMBERS conversion, and nothing works. I have even tried a dozen various VBA suggestions. ALL of the VBA attempts included VALUE(TRIM(CLEAN()) functions...so that can't be the problem.

The only thing that works is >Data - Text to Columns< which causes ALL of the data to occupy it's own cell. Then I have to piece all of the team names back together before I can copy the schedule into my spreadsheet. Aside from that, I have to resort to MANUAL deletion of each number...cell by cell.

Can anyone find out why I am having this much trouble with such a seemingly simple task??
I've attached a sample spreadsheet with the EXACT webpage paste that I am working with.

Any help you can give me will save me TONS of time and work.

[Marcol]

After you have run your macro, (or tag it on at the end)

Try this

Please Login or Register  to view this content.

Cheers

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[HuskerBronco]

Thanks, so much, Marcol...

I attached your code to the end of my macro...and it worked perfectly....sort of.
The code quickly separated all of the rankings from Column C...but it did nothing to the numbers in Column B.
I simply duplicated your code and adjusted it to process Column B after finishing Column C.
Worked flawlessly!!!

A few questions, though, when you have time...

1. Is there any way I can combine these two Columns (B & C) to be sorted by ONE set of code??
2. What if I needed to separate numbers from an entire worksheet??
and...
3. Can you take a look at my original macro and tell me if there is any way to condense the code that I used to FIND / REPLACE certain unwanted text from the original paste??? (Basically, I have a REPLACE statement for each item. Can I do it all with one statement??)

Thanks, again...for you invaluable help.

[Marcol]

Will do, but not tonight, it's late here and I'm packing up for now.

Look back about 20:00 GMT

Tips:-
1/. Add a location to your profile, it helps with prioritising replies according to time zones and any possible language interpretation problems.

2/. Add the version of Excel you are using there are significant differences between them and it helps to know when answering problems.

[DonkeyOte]

Not sure if the below is of interest or not - but a slight re-work to your original code:

Please Login or Register  to view this content.

I would suggest testing on a copy of your sheet....

As a general rule avoid selecting whenever & wherever possible as this slows your code - for general overview re: good coding practices see:

http://blogs.msdn.com/excel/archive/...practices.aspx

I wouldn't claim to be an exponent of all of the advice myself but I do my best (when I remember)

[Marcol]

Hi

I've gone through your code and tidied it up a bit, and added a few extras, hope you don't mind, they are easy to remove if you don't want them.

Part of the coding comes from DonkeyOtes' Post #5.

All the code is in one module and split into sub-routines to make it easier to follow.
Sub-routines are made private to make them invisible to Tools > Macro > Macros........

Have a look at the versus extra the method could be useful in the future.

Also the use of range variables to reduce the ammount of editing required

Have fun !!!!...........

Slainte
Alistair

[DonkeyOte]

I have to confess I thought the premise was simply to remove the rankings from the names where they exist - hence use of Evaluate to strip the numbers
(the code provided previously was simply a tidied up extension of earlier code with rank removal in place - apologies if I missed the point).

[HuskerBronco]

You guys are unreal...!!!!

DonkeyOte...

I never got a chance to test your code, but I can see that you addressed each of the issues from my post to Marcol.
You have come through for me SOOOO many times in this forum!!!
I knew that arrays could be included in VBA code...but I couldn't remember how to write the code.
Thanks for steering me in the right direction...again.

Marcol...

The code that you prepared worked perfectlly, and I have included the entire procedure into my project.
What probably took you the time it would take boil an egg...would have taken me hours to figure out.
I especially appreciate the trouble you went through to explain each step of the process.
I can usually figure out MOST of the suggestions I receive in this forum...
but, when I am lost...I am COMPLETELY lost...!!!

As per your suggestion, I have updated my profile. I wasn't aware that I had never even completed my profile.
I feel like a moron for not including my version of Excel with my post.
It's good to know that the forum can do that automatically.

Thanks, again...to both of you.


I do have one more little question for whichever one of you wants to field it.
It pertains to the array formula in your VBA examples.

In my spreadsheet, all of the unwanted text was replaced with the same thing...(a blank).
How do I write the code for a SECOND array of unique replacement values for each item found??

Example: (1st array) [2nd array]

(Monday) = [M]
(Tuesday) = [Tu]
(Wednesday) = [W]
(Visiting Team) = [""] ---blank
(Home Team) = [""] ---blank

Thanks, again...for taking time to respond to ALL of our questions.

[DonkeyOte]

You could create a 2nd Array yes, eg:

Please Login or Register  to view this content.

Given both vReplace & vReplace with have same dimensions you can then use:

Please Login or Register  to view this content.

You could if preferred store the table of terms in a range on a sheet and conduct a lookup or store as a single 2 dimensional array or split the terms with a delimiter in the single array and use Split
(I say the above because I'm not sure if you can create a (pre-populated) multi dimensional variant array in one go unless from a range - you probably can - I just can't think of the syntax)

[HuskerBronco]

I have to confess I thought the premise was simply to remove the rankings from the names where they exist - hence use of Evaluate to strip the numbers
(the code provided previously was simply a tidied up extension of earlier code with rank removal in place - apologies if I missed the point).

You didn't miss the point at all.
Every method I tried to simply strip the numbers ended in failure.
The only way I could get the numbers OFF of the spreadsheet, completely, was... >>>Data--Text to Columns<<<
...or, manually edit each cell.

Marcol's solution was much more desirable.
I simply added code to Marcol's macro to delete the columns containing numbers.

If there is a way to strip the numbers on an entire sheet...I would like to see it.

Incidentally, I pasted your code into a copy of my spreadsheet, and executed it.
It resulted in #NUM! errors in both Columns B, and C. (the entire column)

Could you look over your code and see if maybe the procedure needs to be corrected??
I "commented out" the whole procedure, and ran each step individually.
Here is the code that produces the above-mentioned result:

Please Login or Register  to view this content.

If you can get this procedure to work...I can incorporate it into Marcol's code...and I'll be in VBA heaven.

I might add, that your code was the first instance I've ever seen involving the EVALUATE function.
I did a web search to learn more about this function, and I must say...it looks VERY promising.
It seems that you can add a great deal of flexibility to your VBA code with this function.
When I get this project finished...I will set out to learn more about the EVALUATE function.
Thanks for introducing me to this previously unknown function.

[DonkeyOte]

Incidentally, I pasted your code into a copy of my spreadsheet, and executed it.
It resulted in #NUM! errors in both Columns B, and C. (the entire column)

Could you look over your code and see if maybe the procedure needs to be corrected??

Without seeing your file I'm afraid I can't really comment as to the resulting error values - the code provided was based on your sample and worked without incident (for me at any rate).

Attached is your file with sample sheet and suggested code in place - running Macro1 generates expected (if perhaps not desired - unknown) results.

If nec. create a copy of the sheet first - so you have a roll back.

I might add, that your code was the first instance I've ever seen involving the EVALUATE function.
I did a web search to learn more about this function, and I must say...it looks VERY promising.

Regards Evaluate - yes it's powerful but not overly intuitive.
As a general rule in terms of speed when processing ranges iteration/looping is slowest, Evaluating via batch generally quicker - processing via Arrays quicker still where viable.
Where there are conditions to updating a range en masse I like Evaluate as I am at heart a formula geek - it does have its limitations though.

[Marcol]

Hi

See if this is any better I've taken out the rankings alltogether and amended the arrays.

I have been trying to get Dons' code to work but it's got me beaten for the moment, I can get it to work on single cells but not on a range I think there is a couple of typos in the string build-up.

Evaluate is a powerful function but it's a bit fickle in it's syntax. So far I've got 3 different results and umpteen errors from the same formula using the various method it employs!

Hope this helps

Alistair

[DonkeyOte]

I have been trying to get Dons' code to work but it's got me beaten for the moment,

I confess I'm slightly baffled by all of the comments re: my code not working - are you saying that regards the attachment I uploaded* the code does not work for you ?
(*a copy of the original with suggested code in place)
On an aside in the way I've coded it, it would be open to error were you using R1C1 reference style as default @ Application level - romperstomper has pulled me up on that on numerous occasions

Rest assured I'm not advocating my approach over any other - just curious.

(The speed gain of using Evaluate over Iteration/Loop is in this instance of no significance ... were it relevant then chances are it would be worth investigating use of intermediate Arrays in pref. to Evaluate also)

[Marcol]

Hi Don,

It failed with me when I tried the file you zipped on Post #11. Just checked it again in case I got the file mixed up still no go.

Just fills "B:C" with #NUM! right down to rows.count

This bit ........FIND("" ""," & .Address & "&"" "")..............
returns......................................$B:$C&
I took it to be ................. & .Address & ","" "")..............

Still no joy

Works fine as a formula on its own I gave it single cell refs used fill down then paste special.

I thought it might have been the absolute refs returned by .Address

But all seems to fail when it goes into Evaluate("........") , I tried all sorts of combinations

putting it into a variable and then = [strQry] gets a correct result but it simply will not run.


I have to much respect for you to think its wrong but something is and I'm blowed if I can find it

[DonkeyOte]

Ah, no, that makes sense, my bad.

Evaluate will process as an Array and pre XL2007 use of entire column references would generate #NUM! errors - oversight on my part.

If you restrict B:C it should work, ie:

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

(in my defence I'm down with "man-flu" )

[HuskerBronco]

I just made the suggested adjustment to your code, DonkeyOte...and PRESTO....!!!
Not only did I get the desired result...but the macro ran lightning fast, to boot...!!!

Many, many thanks to you, DonkeyOte...and to you, as well...Marcol.

Now I can continue on with the next step in the development of my workbook.

I hope that those who viewed this thread early on, stuck with it...(after I marked the thread solved).
There is so much that can be learned from you guys.

[Marcol]

Hi Don
That was the problem, I wouldn't have solved that in a month of Sundays!!!!

HuskerBronco

On the question off Arrays

In this instance you only need one array and this need not contain "Monday"......."Sunday"

Please Login or Register  to view this content.

can just be

Please Login or Register  to view this content.

In the procedure "TidyAndFormatColumnA" in my code

Please Login or Register  to view this content.

That should trim a few nanoseconds!

Cheers