Trim Funtion from HELL

[jmarvino]

Hi guys! I'm new here. But I have a really mind numbing question that may be really easy to you all.


I have a string of "Names" that I need to extract the first word from (which was easy), but I also need to extract the last few words AFTER that point.



For example:
Red Box Underwear


I used the following to extract the "Red":
Left([Names],(InStr(1,[Names]," "))-1),"")


How do I extract the rest of the wording ("Box Underwear")to another field??

 

[isladogs]

Well you got my attention with that title

The expression you wrote for the first word should be
Left([Names],InStr([Names]," ")-1)

For the rest of it, you could use
Mid([Names],InStr([Names]," ")+1)

Also, welcome to AWF

 

[jmarvino]

Holy moly... it worked! See...??? Something that simple. Wow.

THANKS isladogs

 

[isladogs]

You're welcome

 

[jmarvino]

Here's another interesting puzzle:

Say if I had TWO strings:
1) The Institute for Insane Resource Management
2) Bobs Company

I need to split these into two fields, with 30 character limits, as evenly as possible. Without braking a word. How can I get that to work??

 

[kevlray]

Why does this sound like a school assignment?

 

[jmarvino]

Nope. This sadly is real life.

 

[Minty]

Len and Mid functions are you're friends here, but you'll need to be a bit creative.

 

[plog]

I need to split these into two fields, with 30 character limits, as evenly as possible. Without braking a word.

Now its time for a custom function. You create a module and in it write a function to do that. I would use the split function (https://www.techonthenet.com/access/functions/string/split.php) and the Len function (https://www.techonthenet.com/access/functions/string/len.php)

Pass it your full field value and use the split function to seperate it into an array of individual words.
Build string1 by adding words from the array to it, first checking to make sure the next word added will not push string1 over 30 characters.
Once you find the word that pushes string1 over 30 characters you are done with string 1.
Then you dump all the unused words into string2.

 

[Mark_]

What happens if you have more than 61 characters in your original field? Likewise what if either segments is more than 30 characters?

 

[jmarvino]

The destination fields are more restrictive... 30 characters only. Both of these two fields have to be populated, so distributing an even amount of wording would be boss. Juuuuust in case we get a company name that is 60 characters long. Anything above 60 characters will just have to be abbreviated in some fashion but is highly unlikely.

 

[kevlray]

I just hope that any one of the words would not be more than 30 characters long. Plog's suggestion is probably the same route I would take.

 

[jmarvino]

I just hope that any one of the words would not be more than 30 characters long. Plog's suggestion is probably the same route I would take.

Only thing is, I don't think i can use the split function in a query. I may be wrong...

 

[isladogs]

Only thing is, I don't think i can use the split function in a query. I may be wrong...

Correct - but there is a work round courtesy of Daniel Pineault:
https://www.devhut.net/2016/01/14/ms-access-vba-split-data-into-related-table/

 

[plog]

Only thing is, I don't think i can use the split function in a query.

Correct. That's why I suggested a custom function in a module.

 

[Mark_]

If you create your own function, you can pass it a parameter to return first half or last half. In your query you would use something like

MyFieldHere: MyFunction( StringToSplit, 1)
MyOTHERFieldHere: MyFunction( StringToSplit, 2)

and if your function you would look to the second parameter to know which half to return.

 

[jmarvino]

Ah, thanks Plog and special thanks to Isladogs and Mark!