Enter Time, remove text and characters

[Thomas Kuriakose]

Respected Sirs,

We have a report which needs to be converted as per the output tab.

The input tab has the following -

header Time: 9/3/16 - 13:57:52:428 which needs to be converted to hh:mm:ss (13:57:52) for example
header Group: STAT_LT (7) - which needs to be changed to STAT_LT only, remove the (7) for example
header ID: ACT_SERV (1) - which needs to be changed to ACT_SERV only, remove the (7) for example
header Data: 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 10, the characters in this cell needs to be totaled after removing "," and spaces and any two or three digit needs to be counted as one and the result should be - Total characters in brackets (16) followed by a space, equal sign and then the digits with the spaces.

Kindly find attached the input and desired output file for your reference.

Thank you very much,

with regards,
thomas

 

[xlstime]

Hi Thomas,

See if this helps.

Respected Sirs,

We have a report which needs to be converted as per the output tab.

The input tab has the following -

header Time: 9/3/16 - 13:57:52:428 which needs to be converted to hh:mm:ss (13:57:52) for example
header Group: STAT_LT (7) - which needs to be changed to STAT_LT only, remove the (7) for example
header ID: ACT_SERV (1) - which needs to be changed to ACT_SERV only, remove the (7) for example
header Data: 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 10, the characters in this cell needs to be totaled after removing "," and spaces and any two or three digit needs to be counted as one and the result should be - Total characters in brackets (16) followed by a space, equal sign and then the digits with the spaces.

Kindly find attached the input and desired output file for your reference.

Thank you very much,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided.

All the headers are perfect except the Data header output.

The values are correct, only the entry should start with -

(16) space equal sign space followed by the numbers without the commas -

I2 for example should be :

(16) = 1 1 0 0 0 0 0 0 0 0 0 0 0 0 3 10

Thank you very much once again,

with regards,
thomas

 

[bosco_yip]

Try......

1] Output Sheet "Time" A2, formula copy down :

=--MID(Input!A2,10,8)

2] Output Sheet "Group" B2, formula copy down :

=LEFT(Input!B2,FIND("(",Input!B2&"(")-1)

3] Output Sheet "ID" C2, formula copy down :

=LEFT(Input!C2,FIND("(",Input!C2&"(")-1)

4] Output Sheet "Data" D2, formula copy down :

="("&LEN(Input!D2)-LEN(SUBSTITUTE(Input!D2,",",""))+1&") = "&SUBSTITUTE(Input!D2,",","")

Regards
Bosco

 

[xlstime]

Change the Data formula with below formula

="("&LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1&") = "&SUBSTITUTE(D2,",","")

Respected Sir,

Thank you very much for the solution provided.

All the headers are perfect except the Data header output.

The values are correct, only the entry should start with -

(16) space equal sign space followed by the numbers without the commas -

I2 for example should be :

(16) = 1 1 0 0 0 0 0 0 0 0 0 0 0 0 3 10

Thank you very much once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided.

This is perfect.

Much appreciated,

with regards,
thomas