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

 

[XOR LX]

Hi.

Will those two dashes always be the only two dashes within a given string?

Regards

 

[Joe4]

You should be able to do this in a query using a Calculated Field.

Assuming that you always will have exactly 2 dashes in each string, this should work:
MyString: Mid([FieldName],InStr([FieldName],"-")+1,InStrRev([FieldName],"-")-InStr([FieldName],"-")-1)

 

[AndyDevine1]

Yes, thanks

 

[XOR LX]

Yes, thanks

Sorry, but it's not clear if you're saying "Yes" in reply to my question or "Yes, thanks" to Joe4 for the solution he provided you.

Regards

 

[AndyDevine1]

Thanks Joe and everyone, that works a treat!! Thanks!!!

 

[AndyDevine1]

Hi guys
just wanted to say thanks again for help...the yes in a previous response was to XOR but seems that was delayed in being posted, so reads a bit confusing above......all good now...have nice evenings all

 

[AndyDevine1]

Hi Joe4 and others
Can you please help modifying code below to cope with errors...so if it's an error it returns a different heading eg...Field 1 from table 1

MyString: Mid([FieldName],InStr([FieldName],"-")+1,InStrRev([FieldName],"-")-InStr([FieldName],"-")-1)

Many thanks

Andy

 

[Joe4]

What table/field are you applying the formula to?
What do the values in Table 1 Field1 look like?
Do you want to apply the same sort of logic to it, and just return the value of Field1, unaltered?

 

[AndyDevine1]

Hi Joe
Thanks for your prompt reply

I've used the formula successfully in a make table query...so it reads from a table and creates a new table with the result in a new column

The query fails where the cell is blank or whether there are no "-"...

There shouldn't be any instances where this is the case, but 'just in case', I want to allow for this...and return the original value unaltered

Thanks again