removing unwanted characters from a column

[pavan5]

Hi ,

I have a column B which contains data

External Video ID

382926____130510783____vod://fyi.tv/FYHT1863761708020000

vod://_c_fyi.tv/FYHT1863761708020000

382926____130497710____vod://fyi.tv/FYST1863761708020000

vod://_c_fyi.tv/FYST1863761708020000

382926____129824987____vod://fyi.tv/FYHT1863751707260000

vod://_c_fyi.tv/FYHT1863751707260000 from these I want to extract only a particular id , id part is 6 digits starting from 11th digit from end (reverse of the string) and is of 6 digits in SQL I'm able to get this using various function like below, but not able to get it in excel. example from the first row the id is : 186376 IF LOWER(videoAssetCustomId) LIKE '%vod%' THEN COALESCE(CAST(reverse(substr(reverse(videoAssetCustomId), 11, 6)) AS INT), -99) WHEN videoassetcustomid RLIKE '^.*_.*$' THEN substring(videoassetcustomid, 0, instr(videoassetcustomid, '_') - 1) ELSE COALESCE(CAST(videoAssetCustomId AS INT), -99) END AS INT) AS program_id <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

can someone help me here with this

 

[Rick Rothstein]

Give this formula a try...

=LEFT(RIGHT(A1,16),6)

 

[pavan5]

Give this formula a try...

=LEFT(RIGHT(A1,16),6)

thank you works like a charm !

also if the column contains something like below
192099_00_1035134531626
with out the word 'vod' , the id starts from 1 to 6 digits in the above example the id is : 192099

 

[Rick Rothstein]

also if the column contains something like below
192099_00_1035134531626
with out the word 'vod' , the id starts from 1 to 6 digits in the above example the id is : 192099

See if this does what you want...

=IF(ISNUMBER(SEARCH("vod",A1)),LEFT(RIGHT(A1,16),6),LEFT(A1,6))

 

[pavan5]

See if this does what you want...

=IF(ISNUMBER(SEARCH("vod",A1)),LEFT(RIGHT(A1,16),6),LEFT(A1,6))

awesome !! thank you