1 2 3 Previous Next 32 Replies Latest reply on Mar 24, 2014 2:27 PM by Shawn Wallwork

# Removing lots of characters from a string when the lengths vary??

I'm trying to replicate (and modify) the following Excel equation in Tableau 'speak'.  The data source is Excel.  I need to essentially strip off the beginning & end of the strings; however, the length varies - yet is based on 2 characters.  How do I do this??

Source Field Data:

16000 - Smith, Robert

2068333 - Brandenburg, Francis J

End Result Needed:

Smith, Rob

Brandenburg, Fra

Excel Forumal Currently Used To Remove Left Numbers Only:
=RIGHT(J2, LEN(J2)-SEARCH("-",J2))

I know there's a programmer out there who can help me!!!  Thank you.  Renelle

• ###### 1. Re: Removing lots of characters from a string when the lengths vary??

That was a bit of a tricky one:

RIGHT(LEFT( [Renelle String], FIND([Renelle String],',')+4 )

,FIND([Renelle String],',')-FIND( [Renelle String],'-' )+3)

Let me know if you have questions.

--Shawn

• ###### 2. Re: Removing lots of characters from a string when the lengths vary??

WOW!! You are a GENIUS.  Thank you very much.  It worked beautifully.

• ###### 3. Re: Removing lots of characters from a string when the lengths vary??

Renelle, THANK YOU! I haven't been called a genius in a long time. Feels pretty good, even if it isn't true.

--Shawn

• ###### 4. Re: Removing lots of characters from a string when the lengths vary??

Thank you again so much!  How would I modify the formula to address this situation:

Smith, Robert, MD, MPH

Brand-Band,Heather, MD

Note: In this source data (I have 3 separate sources and only name as the common field – ugh!), the second name has a hyphen & zero space between the last name, last initial & the first name.

Thoughts?  Thanks again for your help.  Renelle

• ###### 5. Re: Removing lots of characters from a string when the lengths vary??

Note to Tableau: While I was able to trial & error my way through this and come up with a solution, this is not the way nested string trims should work. Call me if you care or disagree or think it is at all important. If not, no worries I've figured out your contortion so I'm good to go ....

Cheers,

--Shawn

• ###### 6. Re: Removing lots of characters from a string when the lengths vary??

Renelle, that is alway part of the data-scrub problem. Is there anyway to ID these sorts of 'special cases'?

--Shawn

• ###### 7. Re: Removing lots of characters from a string when the lengths vary??

Do you have anyway of ID-ing the data source? A field we can hook into.

• ###### 8. Re: Removing lots of characters from a string when the lengths vary??

I wish.  I’m pulling data together from 3 separate sources & none containing an ID field.  Long story & that’s healthcare . . .

• ###### 9. Re: Removing lots of characters from a string when the lengths vary??

Renelle I feel your pain. But I am sorry to say I can't conceive of a calculation that will parse your strings unless there is someway of differentiating the three data sources. Is there anything AT ALL that will indicate this is from DS-1 and this is form DS-2 and this from DS-3. If not I'm sorry to say I can't help you, and Tableau probably can't produce the viz you want.

--Shawn

• ###### 10. Re: Removing lots of characters from a string when the lengths vary??

Thank you for your help.  I’m going to push back on the owners of the source data & see if they can pull in ANY field that I can use to link the sources.  Not surprisingly, the names are a mess.  If that’s the only link, then the owners will need to do some clean up & figure out a way that I can use your GENIUS formula consistently.  Thank you very much for your help today.  Renelle

• ###### 11. Re: Removing lots of characters from a string when the lengths vary??

I’m going to push back on the owners of the source data & see if they can pull in ANY field that I can use to link the sources.

Dang Renelle now we're talkin'! If you can get them to give you just a small "hook" we can give them what they want (most likely). "Push-back" is a beautiful thing, especially since we are all attempting to change the data visualization paradigm. Good luck.

--Shawn

• ###### 12. Re: Removing lots of characters from a string when the lengths vary??

Hi Renelle, I feel your pain here. I ended up building a combination of:

- a set of VB functions to strip out all of the possible provider credentials (MD, DO, DMD, DDS, NP, RN, APRN, etc.) because those were not consistent from system to system

- a table with a unique Staff ID and canonical names, and various indicators to do things like note that in system X, the provider's name does not include a middle initial, etc.

- queries that use the VB functions and table to do the mapping of names to our unique Staff IDs

But even that doesn't work all the time because providers get married and divorced, somebody enters a MacDaniel as McDaniel, someone uses a common name or nickname in one system and a legal name in another, etc. Every couple/few months there's a new credential acronym or a name change that breaks those data sources I use that still depend on names.

One possible canonical solution is that every licensed healthcare provider in the US has a unique and public NPI that can be looked up online, for example here: http://npilookup.com/npilookup/

Good luck!

Jonathan

• ###### 13. Re: Removing lots of characters from a string when the lengths vary??

But even that doesn't work all the time because providers get married and divorced, somebody enters a MacDaniel as McDaniel, someone uses a common name or nickname in one system and a legal name in another, etc. Every couple/few months there's a new credential acronym or a name change that breaks those data sources I use that still depend on names.

Oh my! I had no idea what I was getting into! Renelle, Jonathan is your guy. He obviously understands your challenges, and most importantly he is an incredibly gifted Tableau expert.

--Shawn

Thanks Jonathan for taking this over, I appreciate it.

• ###### 14. Re: Removing lots of characters from a string when the lengths vary??

Actually Shawn, I don’t have a solution other than seconding your support for push-back on the data owners.

1 2 3 Previous Next