Separate out a value in a string

[AndyDevine1]

hi there
if I have a table where the character strings look like

SSSS-Hello there-asasas_As.jpeg
DDDD_SSSS-GOOGBE-asasas_As_Somemoretext.jpeg

Is there any way to copy the information between the dashes '-' into a new column in my table?

In this example to get 'Hello there' and 'GOOGBE' into the new column?

Many thanks

Andy

 

[Joe4]

The formula will probably error out if it doesn't find at least two dashes. So, what you can do is imbed the formula in an IIF statement, where you first do a count of the number of dashes that appear. If greater than 1, run your formula, otherwise, return the field.

Here is a link which shows you how to count the number of times a certain character appears in a string: How do I count the number of times a character appears in a string - VBScript FAQ - Tek-Tips
See if you can incorporate that into the formula (should be pretty straightforward), and post back here if you have any issues.

Also, one other thing I wanted to mention. You said that you are using this in a Make Table Query. Note that many times, it isn't necessary to create a new table or a new column in an existing table if you can calculate a field based on what you already have. You can just do your calculation in a query. You can use a query for just about anything that you use a table for (source of a Form or Report, or to export). So you may want to ask yourself if it is really necessary to create a whole other table.

 

[AndyDevine1]

Thanks very much!! I got this to work

BTW create tables is something I've inherited...I agree not best way

thanks again

A