Seperate Number & date with formula

[RAJESH1960]

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
	A	B	C
1	No. & Date	Number	Date
2	44540 / 1-10-2021
3	4454 / 1-1-2021
4	528 / 10-10-2021
5	52863 / 10-1-2021
6	/ 22-10-2021
7
Sheet1

 

[jtakw]

Hi,

Do you need the results converted to Real Numbers and Real Date values?

Also, when there's No number, as in your sample A6, there will still be a slash "/" ?

 

[RAJESH1960]

There is a slash before the date in A6. The number may contain text & numbers, so it will be general format. The date, if shown in dd-mm-yyyy format, will be helpful.

 

[jtakw]

Are all the dates in A already in that format? But need converted.
In other words:

Book3.xlsx
	A	B
10	44540 / 1-10-2021	<Oct 1
11	4454 / 1-1-2021	<Jan 1
12	528 / 10-10-2021	<Oct 10
13	52863 / 10-1-2021	<Jan 10
14	/ 22-10-2021	<Oct 22
Sheet967

 

[DanteAmor]

How about:

varios 28ene2022.xlsm
	A	B	C
1	No. & Date	Number	Date
2	44540 / 1-10-2021	44540	01-oct-2021
3	4454 / 1-1-2021	4454	01-ene-2021
4	528 / 10-10-2021	528	10-oct-2021
5	52863 / 10-1-2021	52863	10-ene-2021
6	/ 22-10-2021		22-oct-2021
Hoja19

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,FIND(" /",A2))
C2:C6	C2	=REPLACE(A2,1,FIND("/",A2)+1,"")+0

 

[RAJESH1960]

This is the expected answer (marked in yellow cells)

Query to seperate number and date with formula.xlsx
	A	B	C	D
1	Number			Date
2	44540	1-10-2021	44470	01-10-2021
3	4454	1-1-2021	44197	01-01-2021
4	528	10-10-2021	44479	10-10-2021
5	52863	10-1-2021	44206	10-01-2021
6		22-10-2021	44491	22-10-2021
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=VALUE(B2)
D2:D6	D2	=TEXT(C2,"dd-mm-yyyy")

 

[jtakw]

I think what @DanteAmor posted should work for you, it was practically the Exact same answer I was going to give.
But since I'm in the US, my regional settings throws an Error with +0 for A6.

 

[RAJESH1960]

How about:

varios 28ene2022.xlsm
	A	B	C
1	No. & Date	Number	Date
2	44540 / 1-10-2021	44540	01-oct-2021
3	4454 / 1-1-2021	4454	01-ene-2021
4	528 / 10-10-2021	528	10-oct-2021
5	52863 / 10-1-2021	52863	10-ene-2021
6	/ 22-10-2021		22-oct-2021
Hoja19

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2,FIND(" /",A2))
C2:C6	C2	=REPLACE(A2,1,FIND("/",A2)+1,"")+0

Got it right with a little editing.
=TEXT(REPLACE(A2,1,FIND("/",A2)+1,"")+0,"dd-mm-yyyy")
Thanks DanteAmor.

 

[RAJESH1960]

I think what @DanteAmor posted should work for you, it was practically the Exact same answer I was going to give.
But since I'm in the US, my regional settings throws an Error with +0 for A6.

Thanks jtakw. I have a similar query which I will post in some time.

 

[jtakw]

Got it right with a little editing.
=TEXT(REPLACE(A2,1,FIND("/",A2)+1,"")+0,"dd-mm-yyyy")
Thanks DanteAmor.

I don't think you should use that edit. It changes the results to Text.
Use DanteAmor formula, just format your result cells dd-mm-yyyy to keep the results as Real Date values.