Req: assist with specific nested substitute formula

[irvpost]

I have logfiles that output data out of order on a daily basis.
the format is xxxxxxx:xxxxxxxx:NNNNNNN.N the lines are variable length but always have 2 or 3 colons separating the text from the last field of numbers. Usually 8 char but have a couple up to 20. I have a formula that i have been trying to get to work but am having trouble with the substitute portion. I need everything to the right of the last :.
here is an example of the data--
Begin***
Voice quality: packet loss: 0.06977094002973731
Voice quality: jitter: 2.278952585494796
Voice quality: round trip delay: 22.318643453028653
Voice quality: estimated RTP bandwidth: 1.496161450704676
Voice quality: call legs with VQM statistics: 12106.0
End***

The formula i was trying to use is-
Begin formula***

=IF(ISERROR(SEARCH(":",A1)>0),"",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))SUBSTITUTE(A1,":","_",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))FIND("_",SUBSTITUTE(A1,":","_",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))+1

End Formula***

It works up until just after i count the number of colons.

Any help would be greatly appreciated as I have about 2 years worth of stats to organize ( 6 or 7 million lines ).

 

[T. Valko]

I have logfiles that output data out of order on a daily basis.
the format is xxxxxxx:xxxxxxxx:NNNNNNN.N the lines are variable length but always have 2 or 3 colons separating the text from the last field of numbers. Usually 8 char but have a couple up to 20. I have a formula that i have been trying to get to work but am having trouble with the substitute portion. I need everything to the right of the last :.
here is an example of the data--
Begin***
Voice quality: packet loss: 0.06977094002973731
Voice quality: jitter: 2.278952585494796
Voice quality: round trip delay: 22.318643453028653
Voice quality: estimated RTP bandwidth: 1.496161450704676
Voice quality: call legs with VQM statistics: 12106.0
End***

The formula i was trying to use is-
Begin formula***

=IF(ISERROR(SEARCH(":",A1)>0),"",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))SUBSTITUTE(A1,":","_",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))FIND("_",SUBSTITUTE(A1,":","_",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))+1

End Formula***

It works up until just after i count the number of colons.

Any help would be greatly appreciated as I have about 2 years worth of stats to organize ( 6 or 7 million lines ).

It looks like you can use the last space character as the target.

Try this...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Note that that will extract the number string as a TEXT value.

Some of your numbers are too long for Excel to evalaute them as numbers so you'd have to extract them as TEXT strings.

 

[irvpost]

Thank you for you concise clean and complete answer. I felt a little silly after seeing how simple you you solved my meandering formula.
Thanks again.

 

[T. Valko]

Thank you for you concise clean and complete answer. I felt a little silly after seeing how simple you you solved my meandering formula.
Thanks again.

You're welcome. Thanks for the feedback!