Microsoft Book: office excel 2003 inside...

How i can combine following formulas? Formula 1: =VALUE(MID(A5,4,2)&"/"&LEFT(A5,2)&"/"&RIGHT(A5,4)) Formula 2: =VALUE(MID(A5,3,2)&"/"&LEFT(A5,1)&"/"&RIGHT(A5,4))What I want is interchane 11/22/3333 and 1/22/3333 to 22/11/3333 and 22/1/3333 respectively.

Hi, I haven't actually combined the formulas you provided, but the formula I am suggesting works on the examples you provided and I think it will work on a reasonably wide range of similar data with "/"s in them including data which may be interpreted by Excel as a date.

Please try this and see if it does what you want.

I have split the formula onto four lines for ease of viewing, so reassemble the four lines into one.=IF(ISNUMBER(A5),MONTH(A5)&"/"&DAY(A5)&"/"&YEAR(A5), MID(A5,FIND("/",A5,1)+1,FIND("/",A5,FIND("/",A5,1)+1)-FIND("/",A5,1)-1)&"/"& LEFT(A5,FIND("/",A5,1)-1)&"/"& RIGHT(A5,LEN(A5)-FIND("/",A5,FIND("/",A5,1)+1)))Regards

Thanks Humar, Works perfectly.

Hi leepak, Glad it worked, and thanks for the feedback.

Regards

Humar

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History