Inserting commas in a variable length text in each cell

[bbabine]

Hi All,

Looking for how I can insert a comma in three places in the following text that is in one cell. I am trying to parse out specific data from the text, but they content is variable.

Below are a couple of samples of the text that could be in the cell

00:0B:86:66:62:E4NARRAPGr00610.1.1.161135.9.62.109RAP-5WN1/1/21.5/21.5/Yes*Wired Portenable5h:28m:18sdisableRE

00:0B:86:66:BE:D8NARRAPGr19210.1.1.9698.206.44.5RAP-5WN1/6/21.5/21.5/Yes*Wired Portenable5d:17h:23m:22sdisableRE

This is how it would look after the commas are inserted, then I can do a text to column conversion.

00:0B:86:66:62:E4,NARRAPGr00610.1.1.161135.9.62.109RAP-5WN1/1/21.5/21.5/Yes Wired Portenable,5h:28m:18s,disableRE

00:0B:86:66:BE:D8,NARRAPGr19210.1.1.9698.206.44.5RAP-5WN1/6/21.5/21.5/Yes Wired Portenable,5d:17h:23m:22s,disableRE

The variability comes in when there is not only h:m:s, but also d:h:m:s

Any help would be appreciated and would save me tons of time.

Thanks!!
-bbabine

 

[Straha]

Is the * after the Yes supposed to be removed as well?

 

[bbabine]

Yes, missed that; the * should be removed.

Thanks!!!
-bbabine

 

[Haseeb Avarakkan]

Hello,

If your all cells contains these 3 texts NARRAP, portenable, disable. Try

=SUBSTITUTE(REPLACE(REPLACE(REPLACE(A1,SEARCH("narrap",A1),0,","),SEARCH("portenable",A1)+LEN("portenable"),0,","),SEARCH("disable",A1)+2,0,","),"*"," ")

EDIT; Or

B1,

=REPLACE(A1,SEARCH("narrap",A1),250,"")

B2,

=SUBSTITUTE(REPLACE(A1,SEARCH("portenable",A1)+LEN("portenable"),250,""),B1,"")

B3,

=REPLACE(A1,1,SEARCH("disable",A1)-1,"")

 

[bbabine]

Thanks Haseeb, the first option worked great except on the comma after "portenable", the "e" at the end is after the comma, not before the comma.

I assume that is easy to fix.

thanks!!
-bbabine

 

[Haseeb Avarakkan]

Glad it works.

Change the +LEN("portenable") to +11

 

[bbabine]

Worked great - Thanks for the help.

 

[pgc01]

Hi

Another option:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A1,18,0,","),"*"," "),"Portenable","Portenable,"),"dis",",dis")