need help with 2 formulas

[RompStar]

I need help with, 1)

202 I need to convert that to 02/02/2010, so basically insert the 0 in front of the month if needed, add year 2010 and then format it, this would help me, actually helping someone else, not even for me.

The 2nd) formula that I need help with is:

say in one cell I have a value of: cat
in another cell I have more data: The cat walked down the street and sat down.

I will eventually sort this by date, so while not perfect, I am trying to make a match atleast by that partial 1 word again more words. So basicalluy if 'cat' is in A2 and the fuller Description is in A3, the formula would go into A4.

Thank you.

 

[anglais428]

1) =DATE(2010,RIGHT(Your cell,2),LEFT(Your cell))

for 2) perhaps something like:
=IF(SEARCH(A2,A3)>0,"Whatever_you_want_in_here",FALSE)

 

[RompStar]

great, thanks for the help, let me try that out, against, thanks!

 

[RompStar]

this formula works:

=IF(ISERROR(DATE(2010,LEFT(A1,1),RIGHT(A1,2))),"",DATE(2010,LEFT(A1,1),RIGHT(A1,2)))

but with values like this: 220 that will convert it to: 02/20/2010

but if I have 1020, I get the wrong date, I think because of the RIGHT(cell,1)

how would I alter this formula using the LEN function,

so that if the cell has 3 digits use: LEFT(A1,1),RIGHT(A1,2),

but if it has 4 digits use: LEFT(A1,2),RIGHT(A1,2)


just not sure how to nest the Len and an additional IF, if that is the best way to do it.

I tried to format the columns as custom: 0000, but that don't work, I get wrong dates for some of them and right for some others.

 

[RompStar]

scary, I think I figured it out..

DATE(2010,IF(LEN(A1)=3,LEFT(A1,1),LEFT(A1,2)),RIGHT(A1,2)))