Any Excel genius' ?

[Mike-hybrid]

Any Excel genius' ?

Ok, so here's the setup.

An Oracle DB has been exported into flat text files, and imported into Excel spreadsheets. The data conversion guys are obviously retarded, as we have a problem with sorting the data in Excel. The total rows are totalling over 650,000 records (phone, contactdate, name, address, city, state, zip, createdate, lastupdate, recruiterassigned, salesassociate, ssn, resumeid). The only problem we are facing right now is an "embedded" apostrophe.

Ok, so first off, obviously the first idea was Find/Replace. Doesnt work. Tried just a find and searched data in cells, formulas, etc. No go. This ' is embedded before EVERY cell's data as of now, making sorting by name, last contact, etc impossible.

Check the attached pic, and if you have any idea a quick way to remove the apostrophe and would like to earn 500k on UOGamers, make a suggestion. Anyone who gets it right receives the 500k.

Here's the pic. Notice rows "O" and "P" are date fields but are to the left of the cell, and row "Q" has been updated by me up until the last 2 cells. Notice the ones that have been fixed sit right (as all date fields do in Excel). Anyway, as of now Im semi-manually removing the ' with EZ Macros, but we are talking about over 2 million cells to update (this macro does like 500 cells per 10 seconds).

So shoot me your ideas, and the winner gets 500k :>

 

[fonis]

Did you try doing apostrophe delineation instead of whatever it defaulted to? (comma or tab)

 

[Mike-hybrid]

Did you try doing apostrophe delineation instead of whatever it defaulted to? (comma or tab)

Unfortunately I was not involved with the data conversion...only the team in Fort Lauderdale was. That's why we have such a fucked up excel spreadsheet in every office's hands now, which Im trying to fix.

 

[Irony]

Possible try using the replace function in Excel formulas under string operators to create a new table based on the old table deriving the new fields from the old fields minus the first character (IE replace first char with " ").

This was all I could come up with from messing with Excel for a bit.

-I

 

[BEARDY]

Maybe u could change the cells to show the date in a different way.. then change it back if it gets rid of it.. like where u change it to currency or time or whatever.. lol.. i have no idea.. i just saw the 500k offer n i'm babbling crap. I can't fit excel on my pc n i've finished college for the summer so i can't use it there to even attempt to find a sollution.. er.. do u mean the apostrophe's in EVERY cell or just the date ones? n do u have any idea what they did to get it there?

 

[Gremio]

Hmm, I don't have any problems sorting a-z or 1-9 when there's a ' before the data. I'm using Excel 2003

 

[Gremio]

Ok, to fix this select a column with dates (click the column letter) go to: Data/Text To Columns...
Then, with delimited checked, click Finish.
This will fix all the date fields at least. Still seeing if there's a way to get rid of the normal text fields.

edit: Ok, for some reason when it's just text it will not overwrite the text with the Text To Columns tool, so here's what you do:
Select the column, go to: Data/Text To Columns...
With delimited check, click next.
Click Next again.
For Destination it'll say something like $A$1 change it to show an empty column (i.e. $AA$1). Then copy this column and paste it over the old incorrect column.

Should take about a minute to fix this doing the above .

 

[Mike-hybrid]

Ok, to fix this select a column with dates (click the column letter) go to: Data/Text To Columns...
Then, with delimited checked, click Finish.
This will fix all the date fields at least. Still seeing if there's a way to get rid of the normal text fields.

edit: Ok, for some reason when it's just text it will not overwrite the text with the Text To Columns tool, so here's what you do:
Select the column, go to: Data/Text To Columns...
With delimited check, click next.
Click Next again.
For Destination it'll say something like $A$1 change it to show an empty column (i.e. $AA$1). Then copy this column and paste it over the old incorrect column.

Should take about a minute to fix this doing the above .

I _believe_ this has worked. Remember this has over 650,000 records, so its currently working it (slow ass work computer....).

Ill repost if this does the trick.

 

[Mike-hybrid]

Nopers, it fixed the number columns, but the text columns are still broken.


[edit]

This is really weird. It fixes all columns with dates and numbers...but anything with text or special characters (the - in the phone numbers) it doesnt fix it.

However, you are definitely on the right track here :>

 

[Gremio]

did you read the edit? Like if the text column is in row $A when you go to the tool, after clicking next twice you have to change $A$1 to an empty column such as $ZZ$1 or whatever isn't used and cut and paste column ZZ over column A

 

[Mike-hybrid]

did you read the edit? Like if the text column is in row $A when you go to the tool you after clicking next twice you have to change $A$1 to an empty column such as $ZZ$1 or whatever isn't used and cut and paste column ZZ over column A

I meant the fact that there are certain columns with only text. The text columns it isnt fixing, but all the date/number columns are now fixed...which is the most important really. We need to sort via last updated date :>

Im messing with it now to fix the text fields, cause some of the people using this spreadsheet will want to sort by Recruiter name, Associate name, etc.

 

[Gremio]

Arggg, lol. We're mis-communicating somehow. I tested before I posted this.
If you do exactly what my edit says in the second response it'll fix columns with only text.

Select the column, go to: Data/Text To Columns...
With delimited check, click next.
Click Next again.
For Destination it'll say something like $A$1 change it to show an empty column (i.e. $ZZ$1). Then copy this column and paste it over the old incorrect column.

In essence you're just specifying an empty column to place the text into. When it does this it leaves out the '. Then you copy the column you told it to put the data in over the old column...arggg.

*yes I'm a pirate.*

 

[Mike-hybrid]

Ok, had to read that a few times, think it makes sense now...testing.

 

[Mike-hybrid]

Well, holy shit. 100% perfection. You da man. Ill be off work at like 7:30-8pm CST to give you your 500k!

 

[Gremio]

yay! you are da man my man

 

[BEARDY]

That should have been MY 500k










probably...........








not....