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.
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
Post a Comment