Extract key data from alpha/numeric string

[isadoko]

OS: XP Pro
XL: 2002

Have long lists of combo alpha/numeric data, eg,

4015 PTC
Sellable Item
4017 PTJ Password
4017 PTJ Password
4017 PTJ Password
PTCollab
PTC
4021 PTJ Password
4015 PTC
4015 PTC

I want to extract the number code if present, and if no number code extract any text, and finally if field blank return "blank"

Thanks Much

 

[NBVC]

Here's one option,

=IF(ISNUMBER(VALUE(LEFT(A1,1))),LEFT(A1,FIND(" ",A1)),IF(A1="","",A1))

 

[isadoko]

NBVC your solution was on the mark--many thanks!

 

[NBVC]

Hi isadoko,

Actually, I should have included something in the formula to help ignore the space at the end of a the numeric string that is pulled with the Left/Find function.

Please change the formula to this: I added the "-1" in the Left/Find function.

=IF(ISNUMBER(VALUE(LEFT(A1,1))),LEFT(A1,FIND(" ",A1)-1),IF(A1="","",A1))

Thanks.

 

[Aladin Akyurek]

=TRIM(LEFT(A2,SEARCH(" ",A2&" ")))

 

[NBVC]

=TRIM(LEFT(A2,SEARCH(" ",A2&" ")))

Using this formula, on the second item in the OP's list only the word "Sellable" comes up. I think the OP wants "Sellable Item"