Substring extraction without "Text to Columns"

[Mordred]

Hi all,
I am unsure how to manipulate substrings without performing "text to columns".
Background:
Our sheet looks for and extracts data from a source based on unique identifiers. At times, the source will change the unique identifiers. Eg: v123456 may change to v654321. I have created some code that performs a cansim number validation check. If the number exists, continue with the update process. If it does not exist, the code will reference the title that corresponds to the cansim number. Eg: v654321 represents "Copper Prices" (the title). If the title is found from the source file, I want to update our cansim number to match the source cansim number. The problem is, the source cansim number is embedded in the source title. Eg: Title "Copper Prices [1003] (v654321)", which is located in one cell. I want to extract just the "v" series number (v654321) and not the rest. How can I extract just the "v" series?
Hopefully I have described this well enough.

Best Regards:

[Andrew-R]

Is the v-number always in brackets?

If so try:

Please Login or Register  to view this content.

[Mordred]

Hi Andrew, thanks for your input. Yes, the v-numbers is always in brackets from the source. I tried what you suggested but it creates a compile error stating "A1" variable cannot be defined. Is your suggestion a function for the spreadsheet side of things? I'll be completely honest, I am not very well versed with the spreadsheet side, I've taken on Excel programmatically, not the other way around. Anyhow, here is the code I am trying to apply this to:

Please Login or Register  to view this content.

[MarvinP]

If the number you want always has the ( and ) characters around it then it is pretty simple.

If "Copper Prices [1003] (v654321)" is in B3 then the formula to extract stuff between ( and ) is:

=MID(B3,FIND("(",B3)+1,FIND(")",B3) - FIND("(",B3)-1)

Other guys like to replace all the stuff to the left of ( with "" and start from there.
You will need to find the formula that you like the best.

[Mordred]

I see the confused emoticon does not work when inside code tags.

[snb]

Please Login or Register  to view this content.

[MarvinP]

Hi Mordred,
You can extract the v654321 number using VBA string functions. There are a lot less of them than in Excel. Find the list at: http://www.techonthenet.com/excel/fo.../index_vba.php
This site shows how to convert cell functions to VBA.

If you need help extracting the string between ( and ) using vba please reply to this and I'll do it.

[Mordred]

Thanks Marvin for the link, I used this and it works:

Please Login or Register  to view this content.