Removing text from the start of cell

[kcaenj]

I have a very large database file with over 150,000 names in it.

It was pulled from a CSV file from a corporate global database of employees.

When importing the database there is some extra garbage text in front of each entry for every variable.

For example for the name there is a the string "CN= in front of every name

for employee code there is an OU= in front of every number

This is common for every variable and the common piece is that an = sign comes right before the data I am interested in.

Is there a way to have a macro remove any data that comes before the = and also remove the = sign from the cell?

There are 20 columns of data and again over 150,000 cases.

Any information would be helpful.

KC

 

[Norie]

I don't think you need a macro, you could use an update query that removes the 'garbage'.

The SQL would look something like this:

UPDATE Employees SET Employees.EmployeeName = Mid([EmployeeName],4), Employees.EmployeeCode = Mid([EmployeeCode],4);

This would strip out the first 3 letters of the EmployeeName and EmployeeCode fields.

 

[SydneyGeek]

Norie, just interested -- don't you need a length parameter for MID? I usually use MID([FieldName],4,LEN([FieldName])-3) to make sure I get it all, and not just the 4th character, but maybe I'm missing something in the SQL syntax.

Denis

 

[tonnic]

No,
you only need the point where the text should start.
You only need the length, if you dont want to select the whole text after the start-point.

 

[kcaenj]

How could you do the same thing, except of there is garbage on the beginning and the end?

thanks,

KC

 

[Norie]

KC

Can you give more information about the data? Is it a fixed length of data you want to remove from the end?

If it is then it should be easily doable but otherwise it might be more difficult.

eg

Data is OU=Employee Name=CN

Expression Left(Mid([EmployeeName],4),Len(Mid([EmployeeName],4))-3) should return Employee Name

Denis

In Excel you need the start and length for the MID worksheet function.

In Access and VBA you only need the start and it returns the whole string from that point.

 

[SydneyGeek]

Thanks tonnic and Norie,

learned another thing today!

Denis