Removing text that appers after beginning numbers

[athomemom20646]

Hello,
I have a spreadsheet of 22,600 items. I have a column that has a product ID number followed by a description. I need to remove the description and have just the ID number. My problem is product ID #'s are not all the same length. Is there a formula or an easy way to remove the unneeded text? Below is an example of some of the items. I need the numbers that are listed in front of the word Miller or even after the word Miller as they are the same #.

TIA Trish

11N19S Miller 11N19S INSULATOR, SLEEVE

121332 Miller 121332 CIRCUIT CARD ASSY,CONTROL MAIN

1215 Miller 1215 REMOTE ASSY, ALUMAPRO 15FT

1216 Miller 1216 REMOTE ASSY, ALUMAPRO 25 FT

1217 Miller 1217 REMOTE ASSY, ALUMAPRO 35FT

1223 Miller 1223 MOTOR,PISTOL PRO

1239 Miller 1239 HEAD TUBE,(A) PISTOL PRO 180 DEG 4 IN

1244 Miller 1244 HARDWARE KIT,LINER/TIPS - PISTOL PRO

124425 Miller 124425 CIRCUIT CARD ASSY,MOTOR CONTROL

1245 Miller 1245 CABLE,CONTROL 25 FT (PISTOL PRO)

124511 Miller 124511 SWITCH,TGL DPST 40A 600VAC SCR TERM

1246 Miller 1246 CABLE,CONTROL 35 FT (PISTOL PRO)

1254 Miller 1254 HEAD TUBE,(W) PISTOL PRO 180 DEG 4 IN

125630 Miller 125630 CIRCUIT CARD ASSY,HALL BOOST

125S20 Miller 125S20 WELDCRAFT BOOT, INSULATOR, 2" (50.8MM)

1269 Miller 1269 CABLE,CONTROL 15 FT (PISTOL PRO)

126984 (3) Miller 126984 BRUSH W/SPRING (3)

1272 Miller 1272 LINER,TEFLON .030-.035 WIRE 4IN PP (FASTIP)

1273 Miller 1273 LINER,TEFLON .047-.062 WIRE 4IN PP (FASTIP)

1274 Miller 1274 #008 SILICONE O-RING (FOR DRIVE ASSEMBLIESS)

128077 Miller 128077 KIT,PUMP REPAIR 134120 PUMP

1295 Miller 1295 CABLE,XR-35A PISTOL PRO W/REMOTE

12GG Miller 12GG WELDCRAFT INSULATOR, GAS LENS

1325 Miller 1325 CABLE COVER FOR REMOTE,A/C LEATHER W/VELCRO 35 FT

134082 Miller 134082 DVD,GMAW TRANSFER (8 MIN)

135184 Miller 135184 DIODE BOARD,

135426B Miller 135426B TIP,CONTACT .062/73 (BULK)

1358 Miller 1358 LEVER,ARM ASSY PISTOL PLUS/PRO

136273 Miller 136273 CIRCUIT CARD ASSY,BATTERY CHARGER

1364 Miller 1364 HEAD TUBE ASSY,(A) PISTOL PRO LINER 60 DEG 6 IN

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Since there appears to always be a space after the product number, this formula should work for you...

=LEFT(A1,FIND(" ",A1&" ")-1)

 

[jtakw]

Hi Rick,

For my own education, why the need for the space " " after A1 (A1&" "), is it a safeguard for situations that may arise?

 

[Rick Rothstein]

For my own education, why the need for the space " " after A1 (A1&" "), is it a safeguard for situations that may arise?

In case you have blank cells within your data or if you copy the formula down past the end of your current data (in anticipation of future data) in Column A, the formula will not display a #VALUE! error.

 

[B___P]

Find will report an error if " " is not found inside string

 

[jtakw]

In case you have blank cells within your data or if you copy the formula down past the end of your current data (in anticipation of future data) in Column A, the formula will not display a #VALUE! error.

Find will report an error if " " is not found inside string

Thanks Rick, & BP, should've realized that...

 

[athomemom20646]

Thanks so much. That worked perfectly. You saved me a ton of time!!!