Combine formulas

Microsoft Book: office excel 2003 inside...
December 13, 2009 at 13:29:58
Specs: Windows XP
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.


See More: Combine formulas

Report •


#1
December 13, 2009 at 17:53:55
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


Report •

#2
December 13, 2009 at 18:07:21
Thanks Humar, Works perfectly.

Report •

#3
December 13, 2009 at 20:04:26
Hi leepak,

Glad it worked, and thanks for the feedback.

Regards

Humar


Report •

Related Solutions


Ask Question