Extract a few words (1 Viewer)
- Thread starter Bumi66
- Start date
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:37
- Joined
- Sep 21, 2011
- Messages
- 14,269
Use the Split function and then choose items (0) to (2)
https://www.excelfunctions.net/vba-split-function.html
https://www.excelfunctions.net/vba-split-function.html
- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 21,468
In that case, you won't be able to use Split() directly. Instead, you could try using a custom function like this one to make it work. For example:
Code:
First3Words: GetMember([FieldName],1) & " " & GetMember([FieldName],2) & " " & GetMember([FieldName],3)- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 21,468
Oh, without using VBA, it would be a little trickier - but not impossible. Hang on...
Edit: Ok, so far, I got the first two words this way:
Code:
Expr1: Left([f1],InStr([f1]," "))
Expr2: Left(Mid([f1],InStr([f1]," ")+1),InStr(Mid([f1],InStr([f1]," ")+1)," "))Edit2: Ok, this is the simplest way I could try to get the third word (by using the alias from the first two words; otherwise, it could get really long if we have to repeat the two previous expressions):
Code:
Expr3: Left(Mid([f1],Len([expr1] & [expr2])+1),InStr(Mid([f1],Len([expr1] & [expr2])+1)," ")-1)
Last edited:
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:37
- Joined
- Sep 21, 2011
- Messages
- 14,269
So I would use the same function and put in a function of my own and then pass through the whole string and which part I wanted, as I expect you are now going to say you want each as separate fields?
Code:
Function MySplit(strString as String, iPosition as Integer)
Dim strArray() as string
strArray = Split(strString)
MySplit = strArray(iPosition)
End Functionand in your query
MySplit(fieldname,0), MySplit(fieldname,1),MySplit(fieldname,2)
If you want them all together then in the function make it as DBGuy proposed?
@Bumi66: you can nest Instr functions nested in Left function in a query as in
but it would be unreliable. You'd need as many nested Instr as there are values to be retrieved, and you'll likely get nothing where there are fewer values than the number of Instr calls or where a value is separated by some other character should that occur. Since you cannot use Split in a query AFAIK, then a custom function is your safest bet as suggested.
Code:
SplitOff: Left([splitme],InStr(InStr(1,[splitme]," ")+1,[splitme]," "))
- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 21,468
Hi. Micron makes a good point. When I was testing my InStr() approach above, the value of [f1] I was using was "one two three four." The expressions I posted above works for this sample data. If I only had "one two three" or event "one two," then those expressions (or some of them) will probably fail. Whereas, using the GetMember() function I recommended earlier would/should be able to handle any missing values. Otherwise, to avoid the potential errors, the expressions would get more complicated because we'll have to add an IIf() condition to make sure the InStr() function won't produce an error.
Thanks a lot everyone I use thebdguy's option and it works great
Expr1: Left([Item]![Description],InStr([Item]![Description]," "))
Expr2: Left(Mid([Item]![Description],InStr([Item]![Description]," ")+1),InStr(Mid([Item]![Description],InStr([Item]![Description]," ")+1)," "))
Expr3: Left(Mid([Item]![Description],Len([expr1] & [expr2])+1),InStr(Mid([Item]![Description],Len([expr1] & [expr2])+1)," ")-1)
and finally
Text2: [Expr1] & " " & [Expr2] & " " & [Expr3]
to put it all back together
Expr1: Left([Item]![Description],InStr([Item]![Description]," "))
Expr2: Left(Mid([Item]![Description],InStr([Item]![Description]," ")+1),InStr(Mid([Item]![Description],InStr([Item]![Description]," ")+1)," "))
Expr3: Left(Mid([Item]![Description],Len([expr1] & [expr2])+1),InStr(Mid([Item]![Description],Len([expr1] & [expr2])+1)," ")-1)
and finally
Text2: [Expr1] & " " & [Expr2] & " " & [Expr3]
to put it all back together
Last edited:
- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 21,468
Hi. You're welcome. We were all happy to assist. But please, keep in mind the other concerns we brought up earlier just in case you run into any issues in the future. Good luck with your project.
I know I have to look more into VBA Codes there are much more possibilities, but that's a little too complicated for me right now and I don't have the time right now to learn it. So far I get around with Queries and Macros, but my database gets bigger and bigger, I probably don't get around without VBA in the future.
Thanks again
Thanks again