how do I take specific characters from a cell?

[neutrino]

I have a code number in column 1 Line 61:

X-A56

In column D of same lime I have Dec ( which is next due date to isue an invoice )

In Column E of same line I have 2010 ( the year next invoice due... Dec 2010 )


I want another cell to show these as:


00561210

( combining the 56 with two leading zeros, turning the Dec into a 12

and then the 10 of 2010 )


Question is HOw? manymanymany thanks.

an answer would be Soooo appreciated.

 

[SirJB7]

Hi, neutrino!


I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question, try this:

="00"&RIGHT(A1,2)&TEXT(E1,"00")&TEXT(D1,"00")


Regards!

 

[senthilkumar_rm]

Hi Nuetrino,


You can also try this as well

="00"&RIGHT(A1,2)&AND(D1="dec")*12+(D1="Nov")*11+(D1="Oct")*10+(D1="Sep")*9+(D1="Aug")*8+(D1="Jul")*7+(D1="Jun")*6+(D1="May")*5+(D1="Apr")*4+(D1="Mar")*3+(D1="Feb")*2+(D1="Jan")*1&RIGHT(E1,2)


Thanks


Senthilkumar_rm

 

[Faseeh]

Hi neutrino,


...Or this one you might try:

="00"&MID(A1,4,LEN(A1)-2)&MONTH(TEXT("1-"&D1&"-"&E1,"MM-DD-YY"))&RIGHT(E1,2)


@SirJB7,

Your formula is giving this result: 00582010Dec

or I might be wrong. Please check and advise.


Regards,

Faseeh

 

[oldchippy]

Hi,


My two penneth, should that be


="00"&MID(A1,4,LEN(A1)-2)&MONTH(TEXT("1-"&D1&"-"&E1,"DD-MM-YY"))&RIGHT(E1,2)

 

[SirJB7]

Hi, neutrino!

Faseeh's comment about my post is right. I apologize, maybe I shouldn't write formulas at night when asleep or drunk... and I don't remember what I did last night

Regards!


@Faseeh!

Thanks for your correction, I missed two points:

adding RIGHT(... ,2) to E1 part

assumed D1 was in date format

Well done!

Regards!

 

[neutrino]

Absolutely Awesome !!

T-h-a-n-k-u guys So Much!

( and yep, I will take note of the green stickies in future)