Search a column of cell for a 4 digit number that always starts with the same two digits

[BusinessPlanner]

Hello,

I need a formula that will search a cell that contains various text and number strings for a specific 4 digit number and return that number to a separate cell.

The 4 digit number always starts with 40 and will therefore be 4001, 4002, 4003 all the way up to 4090.
The 4 digit number will not always be in the same place in the cell (i.e. it could be at the beginning, middle, end, or not there at all).
There will be other numbers in the cell that I don't want returned and other text. A sample is below.

I have been using 'Text to columns' to separate out everything in the cells using spaces and then identify the "40" numbers but this takes far too long and I am dealing with potentially thousands of cells in a column.

Any ideas would be greatly appreciated.

Thanks!

[TABLE="width: 609"]
<colgroup><col width="812" style="width: 609pt; mso-width-source: userset; mso-width-alt: 29696;"> <tbody>[TR]
[TD="width: 812, bgcolor: transparent"]18964 - T111 - PLW - 4051 - PLW - BRAKE SWITCH STICKING[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18964 - T111 - PLW - 4051 - PLW - BRAKE SWITCH STICKING[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18964 - T111 - PLW - 4051 - PLW - BRAKE SWITCH STICKING[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18966 - T112 - 4023 - CEN - B END MOTOR FLASHED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18968 - T132 - 4084 - SJM - DRIVER USED TIME OFF FASSI WHICH WAS 4 MINUTES LATE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18970- VERTICAL DAMPER FAULT-121-4077-HEB-PLW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18970- VERTICAL DAMPER FAULT-121-4077-HEB-PLW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18970- VERTICAL DAMPER FAULT-121-4077-HEB-PLW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18970- VERTICAL DAMPER FAULT-121-4077-HEB-PLW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18970- VERTICAL DAMPER FAULT-121-4077-HEB-PLW[/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

This should work, as long as there is the single 40 in the cell.

=TRIM(MID(A2,FIND(40,A2),4))

With something like this example, it returns 40-

18940- VERTICAL DAMPER FAULT-121-4077-HEB-PLW

Howard

 

[BusinessPlanner]

This should work, as long as there is the single 40 in the cell.



With something like this example, it returns 40-


Howard

Thanks very much that works great. There will be an issue when the number 40 appears elsewhere in the cell as you point out but this will cover the majority.

Thanks