String extraction

[pytheus]

Good morning all,

Working on a string extraction formula using a couple cells that i need to have in a defined format. Essentially i need the format to be the same as the following : IIMS:2014-08-01-00001
The string extraction needs to look up 2 different cells to get this data. One which contains the date (C2), the other contains the last number (D2).

What i have been working with so far is ="IIMS:"&year(C2)&"-"&Month(C2)&"-"&Day(C2)&"-"&MID(D2,FIND("S",D2)+1,FIND(":",D2)-FIND("S",D2)-1)

The mid part isnt the most elegant so i imagine i can tidy that up, but the main issue is the output format is not right as per below.

Result - IIMS:2014-8-1-1
Expected - IIMS:2014-08-01-00001

See attached for a probably less confusing explanation.

Cheers,

Pytheus

[mehmetcik]

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.

[pytheus]

Cant believe i forgot "text" -.-v I couldnt work out why i couldnt use the text format. sigh. 5 weeks holidays and my brain goes to mush.

Thankyou very much for your help mehmetcik.

[Richard Buttrey]

Hi,

Perhaps

Please Login or Register  to view this content.