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

Frank S.

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.

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)), " ")

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)), " ")