Extract alphanumeric values from the cell with a specific prefix

[akmalkhan]

Hi,

I have this requirement to be able to extract L%%%%%%%% or W%%%%%%%% values from a cell. I need the L%%%%%%% or the W%%%%%% copied from the source cell and pasted in another cell on the same row.


I have found macros on Chandoos that can extract the %%%%%%% without the 'L' or the 'W'.


Can anyone help me with this pleaaassee..


sample cell content:

Alex Termination of L11177461 M, LIMCLBW / S1224134G wef 8/4/2013

Han Seat Termination - W11143683 for John S78876543F ) effective 30/4/2013

 

[NARAYANK991]

Hi Akmal ,


Can you either upload a sample file which has much more data , or at least copy + paste at least 10 to 15 cells of data , so that we can look for a pattern.


If you can extract the 8-digit number which follows the 'L' or 'W' , then copying the entire string ( including the 'L' or 'W' ) should not be a problem. All you have to do after extracting the 8-digit number is find it in the original text , and then extract the one character alphabet preceding the 8-digit number.


Narayan

 

[akmalkhan]

Hi Narayan, thanks for the reply. Below is the sample data( copy+pate). It is slightly tricky because the same cell can have more than one alphanumeric and I am looking at extracting only the ones that start with L or W.


Notes

Tan is requesting Termination for Ati S811E/L11102787 wef 14 March 2013

Choo Chor requesting Termination (W11162873) for user (S159F / CPFLMS) eff 16 March 2013

"Tan Jing requesting termination (L11165762) of Chris (S786A) wef 15 Mar 2013

RED: 01/04/2013 20:34:06"

Tan requesting Termination for Kan (S898A) for machine L11171145 wef 12 March 2013

Fausiah requesting Termination for Zakiah for machine L45001779 wef 15 March 2013

 

[NARAYANK991]

Hi Akmal ,


You can use the SEARCH function with wildcards , as follows :


=SEARCH("W????????",A1)


=SEARCH("L????????",A1)


This assumes that the number following the 'L' or 'W' is always 8 digits ; also that both 'L' and 'W' will not occur in any cell. The above will also find the 'w' in "wef".


After extracting the 8 characters following the 'L' or 'W' , we can use the VALUE function to convert the extracted string to a number and check whether the value is greater than 0.


Narayan

 

[akmalkhan]

I had used the search function earlier. The output when used on the above pasted data is


45

35

34

56

55


instead of

11102787

11162873

11165762

11171145

45991779

 

[shrivallabha]

For the data posted, try:


=--MID(A1,MIN(FIND("L"&{0,1,2,3,4,5,6,7,8,9},A1&"L"&{0,1,2,3,4,5,6,7,8,9},1),FIND("W"&{0,1,2,3,4,5,6,7,8,9},A1&"W"&{0,1,2,3,4,5,6,7,8,9},1))+1,8)


Edit: if the search for L/W is not case sensitive then SEARCH can be used as Narayan has demonstrated with wild cards. The formula will reduce down to:

=--MID(A1,MIN(SEARCH("L?",A1,1),SEARCH("W?",A1,1))+1,8)

 

[akmalkhan]

Thanks Shrival... used =--MID(A1,MIN(SEARCH("L?",A1,1),SEARCH("W?",A1,1))+1,8)


i get the output as below.


11102787

11162873

11165762

11171145

45001779


Part B of the issue- how do i get the L or the W prefix to this output?

 

[shrivallabha]

Change the formula to:

=MID(A1,MIN(SEARCH("L?",A1,1),SEARCH("W?",A1,1)),9)

 

[akmalkhan]

It works like a charm!! Thanks Shrival(excuse me if I have got the name wrong) & Narayan!!


Edit : Except if there is a mention of a 'W' or 'L' in any of the previous words. i have around 50 errors encountered.I will attempt to clean those up manually.

If anyone is willing to fix this to be 100% automated, it would be good. I can manage with what i have found for the time being.