Find function

[vmjan02]

Hi,

I have a data in column A as

ASR903/I-AP-VKPM-ENB-0178/VKPMGLNMPAR001/172.16.98.137/PSU fail 2/10 --> a1
ASR903//I-MH-PNJI-ENB-0070//PNJISNGLPAR002//172.16.98.38//p0 unknown --> a2
ASR-903// I-WB-ASSL-ENB-0077 *ASSLNCPRPAR001/172.16.58.119//P1 fail --> a3
ASR903/VDDRRMNGPAR001/I-GJ-VDDR-ENB-0123/IM37511249/P1 PSU is in failed state -->a4 and so on


Is there a function to only get (in column B output)
I-AP-VKPM-ENB-0178 --> from a1
I-MH-PNJI-ENB-0070 --> from a2
I-WB-ASSL-ENB-0077 --> from a3
I-GJ-VDDR-ENB-0123 --> from a4
all the len is 18

Is there a function to only get (in column C output)
VKPMGLNMPAR001 --> from a1
PNJISNGLPAR002 --> from a2
ASSLNCPRPAR001 --> from a3
VDDRRMNGPAR001 --> from a4
all the len is 14

 

[NARAYANK991]

Hi ,

For your first requirement , try this :

=MID(SUBSTITUTE(A1," ", ""), FIND("/I-", SUBSTITUTE(A1," ", ""))+1, 18)

 

[vmjan02]

1st part worked well, but mention below cases its not working

I-BR-BKLC-ENB-0037//BKLCBSCKPAR002//172.16.61.215//A900-PWR550-D//ps, fail
*I-KA-BGLR-ENB-6143//BGLRTDSRPAR001//172.16.67.181//PSU1 Fail


output:- I-BR-BKLC-ENB-0037 -- not working with formula
output:- I-KA-BGLR-ENB-6143 -- not working with formula

 

[NARAYANK991]

Hi ,

Text extracting functions will work only if the data follows a fixed set of rules ; if you can either specify exactly how we can go about identifying the required sub-strings , or if you can post enough data that includes all possible variations , it would help.

 

[admiral100]

Hi.
Try this :

=MID(A1,FIND("I-",A1),FIND("ENB",A1)+ROW($A$8)-FIND("I-",A1))

 

[NARAYANK991]

Hi ,

See if this works :

=MID(SUBSTITUTE(A1," ", ""), FIND("I-", SUBSTITUTE(A1," ", "")), 18)

 

[vmjan02]

Hi ,

See if this works :

=MID(SUBSTITUTE(A1," ", ""), FIND("I-", SUBSTITUTE(A1," ", "")), 18)

Yes this worked well
Hw abt the second part

 

[NARAYANK991]

Hi ,

The problem is that the data does not follow a set format.

In some cases , the wanted sub-string is to the right of the first sub-string , while in other cases it is to the left.

How exactly can we identify this sub-string ? The only thing known about it is that is 14 characters long.

Can you give any pointer ?

 

[steve the fish]

Looks like it always ends in PAR00?/ to me?

 

[vmjan02]

pointer would be it could be before "//" or "/" or after "//" or "/"