Extracting string from a cell

marwinrj

hi all,



would love some help here for this--

I have a column that contain these strings in a

cell:

"relates to ABC-25277, relates to AF-4329"

"is caused by ABC-33334, relates to ABC-33362, relates to AF-10303"



I want to extract the "ABC-25277" from the cells and populate into a new column.



 

Sumbunnie

Is the "relates to ABC-25277, relates to AF-4329" all one string in one cell?  And do you want to return just the ABC-25277 or both ABC-25277 and AF-4329?  

If you want to return both codes you could try the SUBSTITUTE formula to remove the "relates to " text:

=SUBSTITUTE([ColumnName]@row, "relates to ", "")

If you only want the ABC code then maybe something like

=MID([ColumnName]@row, 12, 9)

I'd have to think about the one with 3 codes and the "is caused by" text.. You could extract each code using a MID formula into some "helper columns", concatenate them into your new column, then hide the helper columns.  Clunky but would do the job.

Hope that helps!

Paul Newcome

To expand on SumBunnie's solution, you can also nest SUBSTITUTE functions.

 

For example, to remove both strings of "relates to" and "is caused by"...

 

=SUBSTITUTE([Column Name]@row, "relates to", "")

 

will remove "relates to" and leaves the rest of the string. Now you want to take this new string and use the same concept to remove "is caused by".

 

=SUBSTITUTE(first string formula, "is caused by", "")

 

which gives you something like this...

 

=SUBSTITUTE(SUBSTITUTE([Column Name]@row, "relates to", ""), "is caused by", "")

.

You can keep up this pattern for as long as you want until you cover everything you want to remove or until you reach 4,000 characters (including spaces).

 

This solution would leave the remaining data in a single string such as "ABC-1234, AF-9876, DEF-4568"

.

If you wanted to parse them out across individual columns, we would instead use a series of LEFT/FIND and MID/FIND statements.