Extracting a part of a Cell in Excel -


i have particular cell example

cn=cloud test1,ou=corp,ou=bt-users,ou=btatter,ou=test,ou=ad uat,ou=services,ou=managed users,dc=ngco,dc=com 

the number of ou= varies different records, goal extract has ou= infront of it, want:

"ou=corp,ou=bt-users,ou=btatter,ou=test,ou=ad uat,ou=services,ou=managed users"  

as output.

thanks again help!

as array formula:

=textjoin(", ",true,if(left(trim(mid(substitute(a1,",",rept(" ",999)),(row(1:99)-1)*999+1,999)),2)="ou",trim(mid(substitute(a1,",",rept(" ",999)),(row(1:99)-1)*999+1,999)),"")) 

being array formula, needs confirmed ctrl-shift-enter instead of enter when exiting edit mode.

textjoin() available subscription office 365 excel.

enter image description here


if not have textjoin(), put code in module attached workbook , use formula described above:

function textjoin(delim string, skipblank boolean, arr)      dim d long     dim c long     dim arr2()     dim t long, y long     t = -1     y = -1     if typename(arr) = "range"         arr2 = arr.value     else         arr2 = arr     end if     on error resume next     t = ubound(arr2, 2)     y = ubound(arr2, 1)     on error goto 0      if t >= 0 , y >= 0         c = lbound(arr2, 1) ubound(arr2, 1)             d = lbound(arr2, 1) ubound(arr2, 2)                 if arr2(c, d) <> "" or not skipblank                     textjoin = textjoin & arr2(c, d) & delim                 end if             next d         next c     else         c = lbound(arr2) ubound(arr2)             if arr2(c) <> "" or not skipblank                 textjoin = textjoin & arr2(c) & delim             end if         next c     end if     textjoin = left(textjoin, len(textjoin) - len(delim)) end function 

Comments

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

javascript - Replicate keyboard event with html button -

javascript - Web audio api 5.1 surround example not working in firefox -