Jump to content
  • 0

extract value from a text in table data sheet


Question

I have a table in which there is a field that a part of the value in this field needs to be extracted and saved in another column. How can I achieve this? The value always starts with:

CG: and it can be in the middle where it ends with "," or the last value

 

For instance:

GG:American Value Entered, CG:Startup,  MG:Advanced

or

GG:American Value Entered, CG:Startup

Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

You can add a formula field to your table and use the formula below:

CASE WHEN

CHARINDEX('CG:', [@field:FieldName]) != 0 4

THEN 

CASE WHEN 

CHARINDEX(',', SUBSTRING([@field:FieldName], CHARINDEX('CG:', [@field:FieldName]), LEN([@field:FieldName]))) != 0 

THEN 
SUBSTRING(SUBSTRING([@field:FieldName], CHARINDEX('CG:', [@field:FieldName]), LEN([@field:FieldName])), 4, CHARINDEX(',', SUBSTRING([@field:FieldName], CHARINDEX('CG:', [@field:FieldName]), LEN([@field:FieldName])))-4 ) 

ELSE 

SUBSTRING([@field:FieldName], CHARINDEX('CG:', [@field:FieldName])+3, LEN([@field:FieldName])+3) 

END 

END

 

[@field:FieldName] needs to be replaced with the field that has the values in.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...