avoid turning #/## into date when pasting or parsing

[jimtherunner]

I import race data from web sites. I don't want 1st out of 51 which starts
as 1/51 to turn into Jan-51 (which is something like 18600) and I don't want
1st out of 5 to turn into May 1 of the current year. Past special text will
give me a line of text preserving the 1/51 or 1/5, but parsing it into
columns gives jan-51 and may 1 (as well as putting stuff in the wrong columns
due to extra spaces in city names).

 

[Sheeloo]

Paste or import as text.

Let us know how you import so that we can provide further directions...

 

[jimtherunner]

Here's an example website:
http://www.onlineraceresults.com/race/view_race.php?race_id=8877
scroll down and select M7074 (only happens in divisions with <100 or <12
participants) and click search.


Highlight, ctrl+C go to excel and ctrl+V (paste special as text keeps the
slash, but it converts to a date when you parse it) doing some sort of paste
that keeps the slash would be better because parsing introduces a few other
problems (space as delimiter and two word city names).

NO FN LN CITY STATE OVERALL DIVPL SEXPL START 10K HALF 20 Mile
TIME PACE
3135 Walter Schaller Dayton OH 2344 1/9 1684/2429 - 57:04 2:02:13 3:09:43
4:13:29 9:41
4267 Duane Correll Loveland OH 2585 2/9 1830/2429 - 1:02:07 2:10:45 3:20:16
4:22:02 10:00
4952 Charles Kielkopf Columbus OH 3278 3/9 2177/2429 - 1:05:57 2:17:45
3:36:05 4:51:19 11:07
I copy/pasted a few above. It's that 1/9 that turns into September 1,
2008 or if you have 13 to 99 entrants turns into January 1913 to January 1999

 

[Sheeloo]

Ok, here is what you need to do...

1. Copy the table from the webpage (From top left to right bottom corner)
2. Open Notepad,paste and save.
3. Open Excel, Choose File|Open and select the txt file created above.
4. On the Import dialog make sure that delimited option is checked, Click
Next, Click Next, Click on DIVPL filed and choose TEXT as the data type
5. Click Finish and you should be done.

You can also use the following macro (as you do it many times, assign it to
a button) after creating the text file (replace the path and file name with
yours)

Sub Macro1()
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Sheeloo\Desktop\T1.txt", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 2),
Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14,
1)), _
TrailingMinusNumbers:=True
End Sub

 

[jimtherunner]

setting the data type of divpl to text will help with other parsing. thanks.
I should have seen that.

people with spaces in their first name or last name or city name will still
mess up the parsing of text to columns (it will mess up your macro too).

I can avoid the parsing if I paste directly. I think it will be easier to
write my own macros to turn 9-Jan, 9-Feb into 1/9 2/9 and Jan-51 Feb-51 to
1/51 2/51 using the day date month year functions and the & operator. It's
easy enough with functions. I can put a little test first to see if the
column needs any editing and then I only need to have the macro go through
the first 12 or first 99 or to the end of the column, as appropriate.

I was hoping for a way to turn off Excel's I'm going to help you whether you
need it or not attitude.

 

[Sheeloo]

In this particular case, spaces in first/last name won't matter since the
delimiter is TAB.

Yes, it would be great if auto-conversion could be turned off while
pasting...

In the macro
check for year if it is 2008 then return (as string) month/day
else return month/year or 1/year

Let me know if you need help with the macro. I don't think you will need that.