removing unwanted characters from a column

[pavan5]

Hi ,

I have a column B which contains data
[TABLE="width: 687"]
<colgroup><col></colgroup><tbody>[TR]
[TD]External Video ID[/TD]
[/TR]
[TR]
[TD]382926____130510783____vod://fyi.tv/FYHT1863761708020000[/TD]
[/TR]
[TR]
[TD]vod://_c_fyi.tv/FYHT1863761708020000[/TD]
[/TR]
[TR]
[TD]382926____130497710____vod://fyi.tv/FYST1863761708020000[/TD]
[/TR]
[TR]
[TD]vod://_c_fyi.tv/FYST1863761708020000[/TD]
[/TR]
[TR]
[TD]382926____129824987____vod://fyi.tv/FYHT1863751707260000[/TD]
[/TR]
[TR]
[TD]vod://_c_fyi.tv/FYHT1863751707260000

[TABLE="width: 687"]
<tbody>[TR]
[TD="width: 687"]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
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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