Remove non alphanumeric data from cells

**chconnol** · 06-12-2008, 11:04 AM

I have a spreadsheet that we are converting into a database.

There is an invoice number field. In the new database, we do not want non alphanumeric data in these fields. The worksheet source (obviously) has them. For example, we have 1598-07 or IMG-62-45P or 07111 562 (that last one has a space.

So what I need is someway to eliminate non alphanumeric data and spaces so each of the above examples will look like 159807, IMG6245P and 07111562.

I thought this would be an easy one but I can't find any info on this.

Can someone help me once again?

**EdMac** · 06-12-2008, 11:13 AM

have you tried find and replace?

**dominicb** · 06-12-2008, 11:19 AM

Good afternoon chconnol

Put this macro in a module, highlight the data you want to clean and go to Tools > Macro > Macros, Run :

Please Login or Register  to view this content.

HTH

DominicB

**Ron Coderre** · 06-12-2008, 12:49 PM

If the only undesirable characters are spaces and dashes....
perhaps this:

With
A1: (text....eg 1598-07 IMG)

This formula removes those chars:

Please Login or Register  to view this content.

Using the above example,
the formula returns: 159807IMG

Does that help?
Post back if you have more questions.

Remove non alphanumeric data from cells

LinkBack

Thread Tools

Rate This Thread

Display

Remove non alphanumeric data from cells

Remove non alphanumeric data from cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions