Extract text within a String

[Pradeep]

Hi,

I have following type of data:

Range A1: ABCD XXX 18-Feb 11 09:24 - xyz
Range A2: MN XXX 18-Feb 11 09:24 - xy
Range A3: EF XXXXXX 18-Feb 11 09:24 - st

These are details entered by the users after investigation. Information before the date in each of the cell is a fail code. The length of the fail code is not constant.

In the above case Fail code for the respective cells are as follows:
Range A1: ABCD XXX
Range A2: MN XXX
Range A3: EF XXXXXX

I am interested in a formula which can help me segregate the fail code from the text in each of the cell. This is a huge file and appreciate help in this regard.

Please advise

 

[Aladin Akyurek]

Something like...

=LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",2))-1)

 

[Pradeep]

Aladin - You are a STAR. This works.

However, there is one change here. There are a few fail codes which have multiple spaces. Ex-

Range A1: AB CD XXX 18-Feb 11 09:24 - xyz
Range A2: MN XXX 18-Feb 11 09:24 - xy
Range A3: EF XX XX XX 18-Feb 11 09:24 - st.

If I use the formula advised by you, it would return

Range A1: AB CD instead of AB CD XXX and
Range A3: EF XX instead of EF XX XX XX.

Apologies for not providing this information first time.

 

[Aladin Akyurek]

Aladin - You are a STAR. This works.

However, there is one change here. There are a few fail codes which have multiple spaces. Ex-

Range A1: AB CD XXX 18-Feb 11 09:24 - xyz
Range A2: MN XXX 18-Feb 11 09:24 - xy
Range A3: EF XX XX XX 18-Feb 11 09:24 - st.

If I use the formula advised by you, it would return

Range A1: AB CD instead of AB CD XXX and
Range A3: EF XX instead of EF XX XX XX.

Apologies for not providing this information first time.

Does...

=LEFT(TRIM(A2),FIND("#",SUBSTITUTE(TRIM(A2)," ","#",2))-1)

resolve the issue?

 

[Ron99]

Hey Pradeep,

This might work too

=LEFT(A1,FIND(UPPER("X "),A1))

Regards,
Ron...

 

[energman58]

How about

=TRIM(LEFT(A1,FIND("-",A1)-3))

Assuming that the day part of teh date is always followed by "-" if the date is in the form 09-Feb this works as TRIM gets rid of the space and if it is in the form 9-Feb it also works