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

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History