Removing letters from cells (but leaving numbers and special characters)

**C_P** · 12-01-2017, 09:42 AM

Hi All

I'm looking for a formula which might help solve a problem I'm having with some data.

In my spreadsheet I have a column of dates, however, within the same cell, some text can also appear.

EXAMPLE: 01/01/2017 CGHS

Is there a formula I can use to strip out the letters and leave behind just the numbers and the / character?

Thanks in advance.

EDIT:

There can sometimes be two dates within the same cell and the letters are not always fixed in the same place.

Attempted some VBA functions but one stripped out everything except numbers, which ruined the date format.

**Phuocam** · 12-01-2017, 09:47 AM

Try this ...

=MID(A2,SEARCH("??/??/????",A2),10)

**than_gold** · 12-01-2017, 09:48 AM

You can use below formula, if you have the last 4 letters are same in all col or rows.

Formula:

Please Login or Register  to view this content.

**63falcondude** · 12-01-2017, 09:53 AM

Here's another option based on the single example that you have shared:

=LEFT(A1,10)+0

**AlKey** · 12-01-2017, 09:53 AM

Assuming date is in A1
Formula in B1, Format as Date

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

**C_P** · 12-01-2017, 10:03 AM

Sorry, should also have mentioned:

There can sometimes be two dates within the same cell and the letters are not always fixed in the same place.

Attempted some VBA functions but one stripped out everything except numbers, which ruined the date format.

**JohnTopley** · 12-01-2017, 10:10 AM

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**rporter1983** · 12-01-2017, 10:23 AM

Hi, C_P is asking the query on my behalf

Here is the data before and after (although I have another 500 rows to work through).

Thank you,

**Phuocam** · 12-01-2017, 10:44 AM

See this https://www.excelforum.com/excel-pro...ml#post4795061

Use:

=aa(A2,"\d{1,}/\d{2}/\d{4}",CHAR(10))

**rporter1983** · 12-01-2017, 10:52 AM

This is great - have tested on main data-source and is working well.

Thank you!

**mehmetcik** · 12-01-2017, 11:08 AM

Try this formula enterd using Ctrl Shift Enter.

Formula:

Please Login or Register  to view this content.

**AliGW** · 12-01-2017, 12:47 PM

The TEXTJOIN function is only available to Office 365 subscribers.

Removing letters from cells (but leaving numbers and special characters)

LinkBack

Thread Tools

Rate This Thread

Display

Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Re: Removing letters from cells (but leaving numbers and special characters)

Thread Information

Users Browsing this Thread

Similar Threads

Removing non numeric and special characters

Removing special characters

[SOLVED] VBA removing special characters

[SOLVED] Formula for removing special characters.

[SOLVED] reformatting phone numbers (removing special characters "_,-,/, ,(,),etc..."

Removing Text and Leaving just numbers

question about removing text from cells, leaving numbers

Bookmarks

Bookmarks

Posting Permissions