Trim data in Excel 2003

[usmanlatif77]

Hi there

I am trying to figure out if there is a way of getting the 'Desired
output' using Excel formula..see link for the actual spreadsheet.

Any tips/help would be greatly appreciated/

-Usman

http://spreadsheets.google.com/ccc?key=pjjCuXa2gQA-0eWNUvuTvbA

Input Data Desired Output Current solution
aperio_best_plan_subscriber_extract_24095_2009-03-18-20-01-43.log.gz
aperio_best_plan_subscriber_extract
aperio_best_plan_subscribe LEFT(A1,LEN(RIGHT(A1,26)))
test_best_asd_subscriber_extract_245_2009-03-18-20-01-43.log.gz
test_best_asd_subscriber_extract test_best_asd_subscriber_e
test_subscriber_extract_240121295_2009-03-18-20-01-43.log.gz
test_subscriber_extract test_subscriber_extract_24

 

[Jarek Kujawa]

=LEFT(A1,FIND("extract",A1)+7)

 

[EricBB]

try,
=LEFT(A2,FIND("extract",A2)+6)

 

[usmanlatif77]

Sorted!

=LEFT(A13,FIND("_",A13,LEN(A13)-(FIND("_",(RIGHT(A13,26+7)),2)+26))-IF
(FIND("_",(RIGHT(A13,26+7)),2)=3,5,IF(FIND("_",(RIGHT(A13,26+7)),2)
=2,6,1)))

'7' is used as a fiddle factor!

 

[Jarek Kujawa]

my (slightly adjusted) formula gives a desired result

=LEFT(A1,FIND("extract",A1)+6)