Forum Discussion
Calculated Column Exctract text between characters
Hi
I have a column with a text such as:
SOS_name_subname_version_type_requester
or just
name_subname_version_type_requester
and from this column I'd like to extract in a new column just "name_subname_version" so if it's SOS it's between the first appearance of "_" and the fourth appearance of "_". If it's not SOS then it's from the beginning until the third appearance of "_" (so ends up being "name_subname_version" as well).
I tried multiple ideas and while the if statement works I haven't found something to extract between multiple appearances of the same character. Any help is very much appreciated!
Example:
Column Release:
SOS_hello_world_1.0.0.0_initial_me
hello_world_1.0.0.1_update_me
Calculated Column should show:
hello_world_1.0.0.0
hello_world_1.0.0.1
Thank you!
Here’s my understanding of what you're trying to achieve:
- For strings that start with "SOS_":
You want to extract the portion of text between the first and fourth underscores. - For strings that do not start with "SOS_":
You want to extract the portion of text between the first and third underscores.
I used the column named Release and created a calculated column with the following formula to achieve the desired output:
=IF(LEFT([Release],4)="SOS_",MID([Release],FIND("_",[Release])+1,FIND("_",[Release],FIND("_",[Release],FIND("_",[Release],FIND("_",[Release])+1)+1)+1)-FIND("_",[Release])-1),MID([Release],1,FIND("_",[Release],FIND("_",[Release],FIND("_",[Release])+1)+1)-1))--------------------------------------------------------------------------
If this response was helpful, kindly consider marking it as the accepted answer.
- For strings that start with "SOS_":
2 Replies
Here’s my understanding of what you're trying to achieve:
- For strings that start with "SOS_":
You want to extract the portion of text between the first and fourth underscores. - For strings that do not start with "SOS_":
You want to extract the portion of text between the first and third underscores.
I used the column named Release and created a calculated column with the following formula to achieve the desired output:
=IF(LEFT([Release],4)="SOS_",MID([Release],FIND("_",[Release])+1,FIND("_",[Release],FIND("_",[Release],FIND("_",[Release],FIND("_",[Release])+1)+1)+1)-FIND("_",[Release])-1),MID([Release],1,FIND("_",[Release],FIND("_",[Release],FIND("_",[Release])+1)+1)-1))--------------------------------------------------------------------------
If this response was helpful, kindly consider marking it as the accepted answer.
amazing, thank you very much for your swift reply, that worked!
- For strings that start with "SOS_":
