Seperate Number & date with formula

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,206
Office Version
  1. 2019
Platform
  1. Windows
Hello experts
I would like to know how is it possible to seperate the number and date from column A and get the values in the next column B and C,with the help of a formula. The column A has a date value in all cases but has number in some cases only. The number and date are seperated by " / " in all cases. I can use the text to columns option but as the sheet is hidden, I want the formula to auto calculate in diffferent columns.
Book2
ABC
1No. & DateNumberDate
244540 / 1-10-2021
34454 / 1-1-2021
4528 / 10-10-2021
552863 / 10-1-2021
6 / 22-10-2021
7
Sheet1
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,247
Office Version
  1. 2013
Platform
  1. Windows

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,206
Office Version
  1. 2019
Platform
  1. Windows
Done, I formatted the whole column > date > Custom > dd-mm-yyyy, and then entered the formula. Thank you guys.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,247
Office Version
  1. 2013
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,176,168
Messages
5,901,732
Members
434,914
Latest member
mabek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top