Share this content
1

Recommend EXCEL Formula

EXCEL FORMULA

Didn't find your answer?

Good Afternoon

I am a basic excel user, need help with a formula to separate date and text accordingly. Any excel expert feedback will be appreciated.

  1. 16 MAR 17 AUTOTRADER.CO.UK INTERNET GBR GBR
  2. 14 MAR 17 WWW.KITEPACKAGING.CO.U COVENTRY GBR
  3. 13 MAR 17 PAUL UK BROMPTON RD 411LONDON GBR
  4. OT-QINGDAO YINGHE CAP MAKING CO. LTD XIAOYAO VILLAGE LIGEZHUANG TOWN JIAOZHOU

  5. PayPal PayPal PPWDL5U3229UBLZCYC REF-PPWDL

Many Thanks

Jason

Replies (1)

Please login or register to join the discussion.

David Ringstrom
By David Ringstrom
Aug 7th 2017 17:54

If you had dates on every single row then you could use the Text to Columns feature on Excel's Data menu to convert this into two columns without using a formula. The inconsistency in the data makes the formulas a little more complex.

The following formulas assume the first record you listed above is in cell A1, and there are no 1., 2., 3., but instead that the first character is the 1 in 16 MAR 17.

Given that background, put this formula in cell B1:

=IFERROR(VALUE(LEFT(A1,10)),"")

The LEFT function extracts the first 10 characters from cell A1, and then the VALUE function attempts to convert this to a number. If the first 10 characters represent a date, the formula will return a number that you'll reformat using the Short Date format from the Number section of Excel's Home menu. The IFERROR function causes Excel to make the cell appear to be blank if no date is found.

Put this formula in cell C1:

=IF(ISERROR(VALUE(LEFT(A1,10))),A1,MID(A1,11,50))

This time we're using the IF statement to return one of two results based on a logical test. The ISERROR function returns TRUE if the VALUE function returns an error, or FALSE if it does not. If ISERROR returns TRUE then the entire contents of cell A1 are shown, otherwise the first 10 characters (which represent a date) are skipped and all of the remaining characters are shown.

Thanks (0)
Share this content