Replace column value with 3rd comma value

[Marclem]

thread68-1577407

Hello Everyone!

I took a look at the thread above and made the following code:

This is the value located under column E203
CN=Oscarlemile,OU=Users,OU=Tanzania,OU=1_Africa,DC=TNS,DC=ORG

I used this code under column AH203

=REPLACE(E203,FIND(",",E203,FIND(",",E203)+1),LEN(E203)-FIND(",",E203,FIND(",",E203)+1)+1,"")

The result: CN=Oscar Kilemile,OU=Users

What I am trying to accomplish is:

Get only the 3rd text value(OU=Tanzania) into the column AH only

Thank You!

 

[kray4660]

I played around with this and the following formula appears to work.

=MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,(LEN(A1)-FIND(",",A1,FIND(",",A1)+1))-(FIND(",",A1,FIND(",",A1)+1))-2)

 

[AnotherHiggins]

You never actually state exactly what you would want to see based on your example (CN=Oscarlemile, OU=Users, OU=Tanzania, OU=1_Africa, DC=TNS, DC=ORG).

I think you would only want to return
"OU=Tanzania".

Is that right?

First, a question: Can you just use Text-To-Columns?

Assuming I'm right about what you want to return and that you can't use Text-to-Columns, then this should work for you:

[tab][COLOR=green white]=TRIM(IF(ISERROR(FIND(",", A2, FIND(",", A2, 1) + 2)), A2, MID(A2, FIND(",", A2, FIND(",", A2, 1) + 1) + 1, IF(ISERROR(FIND(",", A2, FIND(",", A2, FIND(",", A2, 1) + 1) + 1)), LEN(A2), FIND(",", A2, FIND(",", A2, FIND(",", A2, 1) + 1) + 1) - (FIND(",", A2, FIND(",", A2, 1) + 1) + 1)))))[/color]

or this:
[tab][COLOR=green white]=LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2), LEN(SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2)) - SEARCH("|", SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2))), SEARCH("|", RIGHT(SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2), LEN(SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2)) - SEARCH("|", SUBSTITUTE(SUBSTITUTE(A2, ",", "|", 2), ",", "|", 2)))) - 1)[/color]

I feel sure there's also a much shorter Array formula solution. I'll try to play around to come up with one, but either of the above should work - they're just a bit unwieldy.

What can I say? I'm procrastinating on starting a big project and so I did this instead.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bluedragon2]

Would this work for you:

=MID(A1,FIND("#",SUBSTITUTE(A1,",","#",2))+1,FIND("#",SUBSTITUTE(A1,",","#",3))-FIND("#",SUBSTITUTE(A1,",","#",2))-1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Marclem]

Thanks guys!

It worked for me.