Extracting text from cells

**applesandpears** · 11-21-2012, 06:13 PM

Hi everyone!!!

I would like some help with trying to extract certain different texts from from 1000's of columns of stringtext of file names.

Can you help with the following?

I need to extract certain text such as VGE from the following but its also is other text such as VFIRLE and VFIE into another column.

How would I do this? I cannot do LEFT or MID or RIGHT as it could be anywhere in the cell??

I have tried looking and using different formulas but keep getting an error. This is really making me go crazy!!!

All help is very very appreciated ;-)

File Name Example of what I would like to see
CustomersForOpCo_VGE_20120701000000_431461465201210010006.csv would like to see VGE VGE
SimsForOpCo_VFIE_20120430235959_541540598201209280004.csv would like to see VFIE VFIE
CDAAZVFIRLEC00013.Check would like to see VFIRLE VFIRLE
VoiceDataSessionsForOpCo_VGE_20121019020001_10101155.csv
EventUsageForOpCo_VGE_20121019020001_10101155.csv would like to see VGE VGE
ApiUsageForOpCo_VGE_20121019020001_10101155.csv would like to see VGE VGE
PacketDataUsageForOpCo_VGE_20121019020001_10101155.csv would like to see VGE VGE
SimsForOpCo_VFIE_20121019011501_208.csv would like to see VFIE VGE
PacketDataUsageForOpCo_VFIE_20121019011501_208.csv would like to see VFIE VGE
SimsForOpCo_VFIE_20121019011501_208.csv would like to see VFIE VGE
PacketDataUsageForOpCo_VFIE_20121019011501_208.csv would like to see VFIE VGE
EventUsageForOpCo_VFIE_20121019011501_208.csv would like to see VFIE VGE

Thanks,

Amarjit

**Richard Buttrey** · 11-21-2012, 06:51 PM

Hi,

Apart from the 3rd item I would have said in B2 copied down the following,

Formula:

Please Login or Register  to view this content.

However I don't understand why the last 5 result in VGE when those characters don't exist in the string. Why are these not VFIE

With the 3rd item is there any other rule that could be applied?
e.g. If the string ends in "check" extract the 6 characters starting at the 7th position.

**applesandpears** · 11-21-2012, 07:00 PM

Hi Richard,

Thanks for the quick reply!

I think the text that has been copied is not formatted correctly but in the attached spreadsheet the correct string is there and the corresponding text I would like to extract?

Is it possible to view this and explain how this can be done?

Thanks again for replying to this post - it really is appreciated

**applesandpears** · 11-21-2012, 07:09 PM

Hi Richard,

I tried the formula and it works brilliant!! Can you just explain why there is +1 after A2) and also explain the formula just so I can understand for future use? please?

=MID(A2,FIND("_",A2)+1,FIND("|",SUBSTITUTE(A2,"_","|",2))-FIND("_",A2)-1)

**Richard Buttrey** · 11-21-2012, 08:40 PM

Hi,

The problem boiled down to finding the position of the first & second underscore characters since the value you require lies between the two.
When there is more than one character that you want to find, the usual method is to use the SUBSTITUTE() function to replace a particular occurrence with a new unique character of your choosing.

So here I used SUBSTITUTE() to find the second occurrence of the _ character in A2 and replace it with the | symbol, hence the A2,"_","|",2 parameters in the function.

Then we wrap the Substitute function in a FIND() statement and look for the | character in the now revised Substituted A2 string). This returns the position of the | character in the Substitute strring (i.e. the second _ character in the original A2 string)

Now all we need do is FIND the position of the first _ character which we do with FIND("_",A2). The find always locates the first occurrence.

So now suppose that the | character is at position 21, and the first _ character at 17, we can use the MID() function to slice the original string. Since the find returns the position of the _ we need to add 1 in order to get the position of the first character after the _.

So in the MID Function we then have MID(A2,17+1,21-1) which picks up the three characters VGE

Hope this helps