To Rid Fields Of Strange Social Security Numbers

Mar 16, 2007

weeding out a huge file I have of 10,000 users. When the usernames and social security numbers were entered originally, there were many mistakes on the social numbers, some were longer than 9 numbers and some were shorter....well now I need to delete the rows of the numbers that are nonsense, so I need help in finding or creating a script that goes thru my XLS file and strips out all the rows with less than 9 or more than 9 characters....can someone direct me on how to do this? here is an example of what im talking about

484818227 | John | Doe |
283298383 | Bob | Smith |
2039022221 | James | Dean | <--------need this row removed
293938832 | Billy | Bob |
29832E | George | Bush | <--------need this row removed as well

View 3 Replies


Social Security Number

Sep 8, 2009

if there was a way to block out the last for digits of the social security number? list of numbers are in d4-d20



would be

123-23-XXXX (with X's)

or how ever is supposed to be i forget the (standard) format


View 9 Replies View Related

VBA To Determine If Federal ID Or Social Security #

Jan 29, 2010

What would be the simplest VBA to determine if a cell E7 contains:

A federal ID always 9 digits with only one hyphen which is always after the 1st two numbers. For example: 12-3456789


A social security number always 9 digits with only two hyphens which are always 1 hyphen after the 1st 3 numbers & the other hyphen after the next 2 numbers. For example 123-45-6789.

In other words,

If Range("E7") = a federal id then
If Range("E7") = a social security number then

View 10 Replies View Related

Remove Characters From A Social Security Number Without Removing The Zero's In The Front

Apr 14, 2009

I need to know how to remove characters from the date field in Excel.


9/12/1975 - I need it to read 09121975.

I also need to know how to remove characters from a social security number without removing the zero's in the front.


012-34-5678 - I need 012345678.

View 9 Replies View Related

Create A Worksheet That Will Generate A Random Social Security Number

Jul 30, 2006

For my assignment, I am supposed to create a worksheet that will generate a random social security number (using the correct social security form of 000-00-0000) There are supposed to be a billion social security possibilities and each should be equally likely to be selected in the worksheet.

View 20 Replies View Related

Populate Fields Based On Matching Numbers

Oct 7, 2009

I've been racking my puny brain for an hour trying to figure this out. Basically, my problem is with the last 2 invoices listed. If one invoice has "PO CLOSED" on it, I want all other invoices on that same PO to say the same thing. Surely I can do this with a formula or conditional formatting, but I cannot think of how.

PO #Invoice #DateInvoice TotalDept.ActivityElementPO AmountPO Amount RemainingPO Status0707955289651/12/09$812.9075105854035$2,960.28$59.380707955459806/15/09$667.2475105854035$2,960.28$59.380707956486827/7/09$812.9075105854035$2,960.28$59.380707955584669/29/09$607.8675105854035$2,960.28$59.380719750913336/18/09$947.7975105853305$5,000.00$3,117.210719750915917/17/09$935.0075105853305$5,000.00$3,117.21PO CLOSED

View 9 Replies View Related

Macro Security (saying That The Macro Security Level Is Too High)

Nov 18, 2008

I have a sheet that will not let me use macros. I can record them, but when I go to use them it says that the Macro security level is too high. I change it to Medium or Low but it doesn't make a difference.

The same spreadsheet works with Macros on another computer, but not my main one.

View 5 Replies View Related

Remove A Strange Scroll Bar

Feb 27, 2009

I dont know the real name about this scroll bar (like you can see in my attachment), so I cant search in the forum, sigh! how can I remove the bar in the red circle?

View 3 Replies View Related

Strange INDEX Phenomena?

Oct 8, 2009

INDEX(Sheet1!$F$2:$F$10,0,1) returns #VALUE!
INDEX(Sheet1!$F$2:$F$100,0,1) returns 110 (e.g.)
As Both index have zero(0) for its row reference (row_num) I would expect both formulas to return #VALUE! (or even #REF!)

View 2 Replies View Related

Strange Round Off Error

Jun 16, 2006

when mixing data types: a real and a complex as in


if G1 and H1 are both exactly equal to 4, the answer whould be 0

see [url]
and look at location I1 (highlighted).

View 9 Replies View Related

Strange Characters Appears

Sep 16, 2009

there is a strange character that appears in an excel spreadsheet. It looks like a square with a question mark in it.

what this means and am I able to remove them

View 9 Replies View Related

Strange Characters Equal EOF

May 28, 2006

I made a userform that spits out 'logs' in a more readable easy to understand. It basically shoves everything into a nicely organized list box.

I have a load button that if pressed accepts .csv's

It works perferctly for practically every type of .csv that contains these logs however 1/100 times the logs contain korean or chinese characters which cause the program to crash.

I gone through some debugging and I found out that the characters that cause it to crash happen to equal the EOF character. So not only does it spit an error on that line, it stops loading anything after that point.

Here is my CheckFileSize function that runs first to tell me how long I should make my list.

Private Function CheckFileSize(ByVal TempFileName) As Double
Dim TempData As String
Dim SizeCount As Double

how to do this besides loading it into a worksheet first?

View 9 Replies View Related

Strange Thing Only On One Computer Running This XLS

Feb 4, 2009

Copy of the scaled down Workbook are enclosed. Password is "j". Sheet("Final Schedule") is one of 11 sheets in the Workbook. Range("a10: BE120") is copied from another Sheet and pasted.

ISSUE BACKGROUND: Entries into cells are Data Validation and a drop down on each cell. In cells C15, 16, 17, 18 or O15, 16, 17, 18 First time the Sheet is opened: user has no problem if they select a D from the drop down.

But if user types in D and hits enter key, contents of G10: M10 (merged cells) is copied into that cell. Any thoughts on where to check or what settings to check? It only happens on that one computer.

View 3 Replies View Related

Today Function Works Strange

Aug 24, 2009

I have a column, where i want to calculate the difference between today and another date/or viceversa. The problem is, that in the first two cells, it calculates it well, but then, it shows #VALUE and when I press to see the "calculations steps" , it shows "29/09/2009"-40049. It turns the today function into a number. Why?

View 11 Replies View Related

Referencing To CSV File In Strange Format

Oct 21, 2009

I am working on an excel dashboard to display life information about energy consumption.

there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.

Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7

I can open and view the data by clicking "open as" and selecting excel.

I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.

However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.

I need a solution or a way around this so i can reference from one workbook to another.

Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?

I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...

View 14 Replies View Related

Strange Characters Pasting To Textbox

Dec 20, 2006

it is a very simple marco. It contains a textbox input within a user form. The only problem I have is, when I use CRTL-V to paste some text into the textbox, it has a wired character attached to the end of the text.

for example, I highlighted an email address (ex. from an email, use CRTL-C to copy, and I make sure that there is no space after the .com, and after I pasted using CRTL-V to the textbox, a wired character, like a reversed P sign..... very much like the carriage return in MS Word, would anyone know how to avoid that or disable that extra character?

View 3 Replies View Related

Increase In Decimals Returns Strange Result

Jul 21, 2006

I have discovered a strange thing about excel and it is driving me mad and ruining my project.

Basically, If i enter the number 97.4 into cell A1 and then 97.165 into cell B1 and then enter the formula =A1-B1 into cell C1 i get the answer 0.235. Which is correct.

However, when you increase the number of decimal places to 30 the answer that is returned is 0.234999999999999000000000000000.

This calculation result seem to fluctuate wildly depending on the primary numbers in cells a1 and b1. Very rarely have i seen the full 30 decimal places without a bizarre result like this.

Maybe it has been too many years since i was at school and that i am confused with my numbers and that there is a real easy answer to why this result is returned.

As i said it is ruining my project that i am working on. Can anyone help? Why does excel do this? Is there a way to stop this happening? My project relies on this answer to be correct. Is there a way to force excel to stop doing this?

View 9 Replies View Related

Bulk Convert Strange File Format In Multiple Folders.

Oct 27, 2009

I have a series of files in *.ew7 format (i know its a random program)

I have some code which i can run which will simply open the relevant file and save it as xls.

The files are saved in multiple folders in format "yymm" eg: 0910 - oct 09.

inside each folder there is a ew7 file for each day in rthe format: "01ddmmyy" eg 01271009 = 27th oct 2009.

I have folders going back as far as may 2002 (in folder 0205) and i need a way to convert these all into xls format and save them in the same location.

View 13 Replies View Related

Date Fields Increase Date By Numbers Of Years Based On Lookup Table?

Aug 11, 2013

In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -

d1 e1
Restricted 5

Example of data
a1 b1 c1
Restricted 1/06/2012 1/06/2017

What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.

View 1 Replies View Related

Macro Worked For 2 Years, Suddenly Started Giving Strange Results

Jan 20, 2009

I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.

View 3 Replies View Related

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83


left side pivot created in vb 6.0 & right side pivot table created manually in excel.

i want to generated pivot table using vb 6.0 same as right side pivot.

Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),


View 2 Replies View Related

Are You Sure - Security For Macro

Aug 7, 2007

I have a spreadsheet that I regularly have to clear the data and start over on, so i recorded a macro for clearing this rather long sheet with only one cllick.

My problem is that i am not the only person who will use this sheet, if They click the control button by mistake they will clear their work and you cannot undo it. I was wondering if i could make it ask "are you sure" kind of like the save option when closing an unsaved document.

View 11 Replies View Related

VBA Security Options

Oct 12, 2009

We have developed an Excel workbook with hundreds of lines of VBA code written for a specific application, and we would like to protect it.

Could someone give me an idea of what security options we have over and above the below (if any)?

We have protected all sheets and the workbook itself with passwords. The VBA itself is protected with a passoword.

My understanding is that VBA can be hacked into quite there any way of adding another layer of security/protection without having to move to another platform altogether?

View 6 Replies View Related

VBA Module Security

Dec 17, 2008

i am going to ask is for my curiosity only and to be sure that no one will break my password.
If the VBA module is locked for viewing and password protected, it can't be cracked??I asking this, because i made an excel application with macros, and i don't want the user of it to crack my password.
I have to mention that the password is 17 letter+spaces long.
I repeat again....i asked this ONLY for my application safety.

View 9 Replies View Related

Macro Security

Jun 29, 2006

If there is code attached to a worksheet, not necessarily in a macro, does the macro security still have to be low to use it??

View 3 Replies View Related

Security Code Input

Dec 7, 2009

If I have a cell which has been validated and has a drop down list of 4 names in it.
I need it to prompt the user that when they choose there name and select, a window pop up or similar window appears asking for a 4 digit(or similar) code.

This is to stop others using peoples names against jobs they have completed so there is accountability if there are issues with information.

For each line I need it to re-prompt the user to enter the 4 digit pin so it cannot be tampered with.

Can anyone help with sending through a form and VB code possibly which I can copy into my spread sheet or a method I can use!

View 14 Replies View Related

Cell Value Security Check

Apr 10, 2007

There are 2 cells on my spreadsheet where individuals must populate $ values. I want to put a security feature in the worksheet that would create a pop up warning to the user that one cell must be greater than the other.

B26 (Client Assets) must be greater than B41 (Dollar Impact), otherwise a dialog box pops up to indicate to the user that B41 cannot be greater than

View 4 Replies View Related

Security Against Password Hacking

Dec 12, 2007

Is there a way to make excel less vunerable to hacking? We have a customer who is always breaking in and changing things.

View 4 Replies View Related

Security Warning (Macros Have Been Disabled)

Apr 28, 2008

I was playing around with Macros on an excel file. I created a few and ran them. In the end, I didn't like the way they ran, so I deleted them all. However since I ran them in the excel file, every time I open the file it gives me a warning saying
"Macros have been disabled."

Is there any way to remove the possible Macros ties/links from my file? I know I can disable the warning, but if I just want to remove the Macros ties to my file.

View 10 Replies View Related

Security - Protecting A Paticular Sheet Only

Jan 7, 2009

i have produced a spreedsheet on ms excel.

On sheet 2 is all of the information and tables etc.. for example prices and information for the combo boxes an such...i need to protect this sheet so no one can change anything on it

the problem is when i do so, and go back onto sheet 1 and say tick a checkbox or select something from the combo box is doesnt allow me to, the protection message comes up

i dont want this to come up on sheet 1, only on sheet 2!

i think its because i have links between the pages? like all of the info for the combo box is on sheet 2

View 9 Replies View Related

Copyrights 2005-15, All rights reserved