Extract a Number from a cell, the position of the number varies

Problem: I have a task name column and within this contains a number that I'm trying to extract. The number is always formatted with CRXXXXXX. The location of CRXXXXXX in the column varies from row to row.

Goal: I want to extract the number from the task column.

Example:

CR123456 would be 123456 a new cell.

I have seen several ways to extract if the information is always in the same place.

Find more posts tagged with

Formulas

Accepted answers

Parker Oxford

Will the number always be together? And will it always end with a number?

You could use a variation on the MID formula, i.e.

=MID(Cell@row,COUNT(FIND{0,1,2,3,4,5,6,7,8,9},Cell@row)),X)

Frank S.

I found a solution and thanks to @Parker Oxford for pointing me in the right direction.

Here is my solution:

=IFERROR(VALUE(MID([Task Name]@row, FIND("CR", [Task Name]@row) + 2, 6)), " ")

All comments

Parker Oxford

Will the number always be together? And will it always end with a number?

You could use a variation on the MID formula, i.e.

=MID(Cell@row,COUNT(FIND{0,1,2,3,4,5,6,7,8,9},Cell@row)),X)

Frank S.

@Parker Oxford

Yes the number will always be together and CR123456 will always end in a number.

In reviewing your formula what would the X represent?

Can you provide a little more clarification on the formula.

Thanks!

Frank S.

I found a solution and thanks to @Parker Oxford for pointing me in the right direction.

Here is my solution:

=IFERROR(VALUE(MID([Task Name]@row, FIND("CR", [Task Name]@row) + 2, 6)), " ")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.