Using Right and Substitute together in a formula

[GuyGadois]

For some reason I can't get this to work. Any help is appreciated

AccountSSName1 = John Smith DOD [1234-1234]
Out of that string I want to:
#1 - replace "[" & "]" with "(" & ")" so I can later name a tab of the worksheet with this value
#2 - just keep the 1234-1234

Keep in mind the value in AccountSSName1 will always be XXXXXXXXXX [1234-1234] whereas XXXXXXXXXX can be any length. In other words, change the "[" & "]" to "(" & ")" and remove anyhting past 9 characters from the right.

Here is what I have that I can't get to work. I think I am really close.

=RIGHT((SUBSTITUTE(SUBSTITUTE(AccountSSName1,"[",""),"]",""),LEN(SUBSTITUTE(SUBSTITUTE(AccountSSName1,"[",""),"]",""))),-9)

Cheers,

Guy

 

[FDibbins]

If all you want is the 1234-1234 part, try this...
=MID(A5,FIND("[",A5)+1,9)

 

[GuyGadois]

If all you want is the 1234-1234 part, try this...
=MID(A5,FIND("[",A5)+1,9)

I don't like that solution. It is way too easy and underengineerd

Thank you

 

[Norie]

Guy

Does this give you what you want?

=RIGHT(SUBSTITUTE(SUBSTITUTE(AccountSSName1,"[",""),"]",""),9)

 

[FDibbins]

I don't like that solution. It is way too easy and underengineerd

Thank you

Well pardon me for taking the simple approach and giving you something that works

 

[Aladin Akyurek]

Looks like...

=SUBSTITUTE(REPLACE(A2,1,FIND("[",A2),""),"]","")

where A2 houses an account name.