Extracting string from a cell

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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…