Fellow Apptian's,

I am trying to use the Name column below to create individual Vendor ID and Vendor Name columns, but I'm not able to derive a Vendor Name column due to the inconsistent number of characters. 

• I used =SPLIT(Name,-1," ") to produce a Vendor Id column and that worked as expected
• I’ve tried the Right, Left, Mid, Trim and Split functions, but I can’t seem to get it right in an attempt to produce a Vendor Name column.

I believe removing the Vendor Id on the right, which has a consistent 6 to 8 characters, would work. Any help here would be great!

Thanks in advance!

Have you tried lopping off the characters from Name which are in Vendor ID?

Vendor Name = Left(Name,Len(Name)-Len(Vendor ID)) - maybe wrap that in a Trim() so you don't have to worry about a trailing space. Also, is your Vendor ID column numeric or a label? You could be getting odd results if it is set to numeric.

Donavon,

I'm wondering if you could some how use the Search function to determine how many spaces there are in each row, then use that number from your search column to split the column. I just don't have the right logic determined to do this.

A longer option might be to create three numeric Left function columns for a lengths of 6, 7 and 8.  Then use if functions to compare the sizes of the numbers against each other for the greatest one. Then keep that value. 

Sorry those are two ideas off the top of my head.

Jeff 

Hi @Donavon McLeod,

You can use your original formula to extract the Vendor ID then you can use that in combination with the SUBSTITUTE formula to have it removed from the vendor name.

Vendor ID  =SPLIT(Name,-1," ")

Vendor Name =Substitute(Name,Vendor ID,"")

Good Luck,

Ben

Probably want to apply a "Trim()" to the statement to clear any trailing (invisible) spaces on the vendor text...

I really appreciate the help guys!.

I ended up using the Len function on Name and Vendor ID, then subtracting them to derive the character count (VN HLPR 1) for my Vendor Name formula; Trim(Left(Name,VN HLPR 1)). Worked like a charm and now I can move on to the next one.

Thanks again and goodnight all, 

-Donavon McLeod

Another approach is to use ReplaceRegex function to extract either the name or the id. See regexr.com for more info.

Vendor ID = ReplaceRegex(Name," (\d{6,})$","$1")     - Look for a number at the end 6 digits or longer

Vendor Name = ReplaceRegex(Name,"(^.+) \d{6,}$","$1")     - Look for all the text up to a number at the end 6 digits or longer