special delimiter / text to columns 1

[ksbrace]

I have a column that looks like this:

LOT_2891_2894 CCI
LOT_2991_2993 CCI
Diverse LOT_50176_50188 HSBC Retail 3/12/14
Diverse LOT_50112_50188 HSBC Retail 3/12/14
Diverse LOT 50142_50188 HSBC Retail 3/12/14
Diverse LOT_50231_50243 Credit One
Diverse LOT_2994_2996 Sterling 3/12/14
Diverse LOT 50141_50188 GE Retail

I want to just grab the LOT #, sometimes it has an underscore, sometimes it's a space....

 

[SkipVought]

Hi,

Use Edit/Replace...

Change

Lot SPACE to Lot UNDERSCORE

 

[zelgar]

The following formula should do the trick (assuming the data is in Cell A1):
=MID(A1,LEN(LEFT(A1,FIND("LOT",A1)+4)),FIND(" ",RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("LOT",A1)+4)))))

The resulting data looks like the following:
2891_2894
2991_2993
50176_50188
50112_50188
50142_50188
50231_50243
2994_2996
50141_50188