Quick & simple help needed --> Text manipulation

[snejsnej]

Hello,

I have a long series of values consisting of 3 concatenated values separated by a "|". Eg:

[TABLE="width: 375"]
<tbody>[TR]
[TD="class: xl64, width: 375"]-215.28|6202721174567002|08/06/2013 11:31[/TD]
[/TR]
</tbody>[/TABLE]

The last value is a datetime in the format "mm/dd/yyyy hh:mm".

After the first concatenation operation, I have a couple of in-between steps after which I perform another concatenation:

"-215.28|6202721174567002|08/06/2013 11:" & "32"

...to produce "-215.28|6202721174567002|08/06/2013 11:32"

I am also performing a simple subtraction in the same operation to reduce the minutes by 1, so the above becomes:

"-215.28|6202721174567002|08/06/2013 11:31"

The problem arises when I try to concatenate minute values less than 10, i.e. 01, 02, 03, 04, etc. When I try to do this, I end up with this:

"-15.96|6202721756259002|08/06/2013 11:4"

...instead of this:

"-15.96|6202721756259002|08/06/2013 11:04"

This is the formula I'm using:

=IF(L37="N/A","N/A",CONCATENATE(M37,(L37-1)))

I tried this operation with L37 formatted as TEXT with no luck.

Appreciate any help,
Jens

 

[circledchicken]

Hi,

Does this work:

=IF(L37 = "N/A", "N/A", M37 & TEXT(L37 - 1, "00"))

 

[snejsnej]

Worked great, thanks

 

[circledchicken]

You're welcome - thanks for the feedback.