Trim Funtion from HELL (1 Viewer)

jmarvino

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
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

MVP / VIP
Local time
Today, 15:12
Joined
Jan 14, 2017
Messages
18,186
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

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
Holy moly... it worked! See...??? Something that simple. Wow.

THANKS isladogs
 

jmarvino

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
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??
 
Last edited:

kevlray

Registered User.
Local time
Today, 08:12
Joined
Apr 5, 2010
Messages
1,046
Why does this sound like a school assignment?
 

jmarvino

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
Nope. This sadly is real life.
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:12
Joined
Jul 26, 2013
Messages
10,355
Len and Mid functions are you're friends here, but you'll need to be a bit creative.
 

plog

Banishment Pending
Local time
Today, 10:12
Joined
May 11, 2011
Messages
11,613
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_

Longboard on the internet
Local time
Today, 08:12
Joined
Sep 12, 2017
Messages
2,111
What happens if you have more than 61 characters in your original field? Likewise what if either segments is more than 30 characters?
 

jmarvino

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
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.
 
Last edited:

kevlray

Registered User.
Local time
Today, 08:12
Joined
Apr 5, 2010
Messages
1,046
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

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
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...
 

plog

Banishment Pending
Local time
Today, 10:12
Joined
May 11, 2011
Messages
11,613
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_

Longboard on the internet
Local time
Today, 08:12
Joined
Sep 12, 2017
Messages
2,111
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
Code:
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

New member
Local time
Today, 08:12
Joined
Mar 12, 2019
Messages
7
Ah, thanks Plog and special thanks to Isladogs and Mark!
 
Last edited:

Users who are viewing this thread

Top Bottom