Extract data in two other fields

  • Thread starter Thread starter Dimitris
  • Start date Start date
D

Dimitris

Hello, my problem is that I have in a field, entries with a lot of words.
What I need is have lets say the last 3 words of each entry, deleted from
that entry and entered in a new field. For example I have the entry:
"This company has a very long name" In F1. I want the words "This company
has a" entered in a new field, F2. And the words "very long name" entered in
a new field, F3. Is this possible?

Note that not all entries have the same number of words. But all have more
than 3.

Thank You
Jimmy
 
It can be done, but it's definitely going to take some VBA.

You could write a couple of functions to do this, something like the
following untested air-code:

Function AllButLast3Words(InputData As String) As String
Dim intLoop As Integer
Dim strOutput As String
Dim varWords As Variant

varWords = Split(InputData, " ")
If UBound(varWords) > 3 Then
For intLoop = LBound(varWords) To UBound(varWords) - 3
strOutput = strOutput & varWords(intLoop) & " "
End If

AllButLast3Words = strOutput

End Function

Function Last3Words(InputData As String) As String
Dim intLoop As Integer
Dim strOutput As String
Dim varWords As Variant

varWords = Split(InputData, " ")
If UBound(varWords) > 2 Then
For intLoop = UBound(varWords) - 2 To UBound(varWords)
strOutput = strOutput & varWords(intLoop) & " "
End If

Last3Words = strOutput

End Function
 
Thanks for your answer Doug.
There's something I don't understand. Do I create an update querie or not?
And I write all this in the SQL View.? Copy it as it is? And where do I
write the actual field names?
Sorry but I my knowledge is limited.
Thank you
Jimmy
 
Do you need the change to be permanent, or do you simply need the split to
be available to you?

If you need it to be permanent, you'd add the 2 new fields to your table,
use an update query to populate the 2 new fields (using those functions to
provide the values for the 2 fields), then delete your existing field (since
it would be a mistake to have the data stored redundantly)

if you only need the split to be available to you, use the function in a
query to create two computed fields, and use the query wherever you would
otherwise have used the table.

The two functions I suggested would go into a module. You don't need to make
any changes to them: they don't need to know your field names. You call them
as AllButLast3Words([TheNameOfYourField]) and
Last3Words([TheNameOfYourField])

If there's a chance that some of the fields you'll be passing to the
function might be Null, change the declarations to

Function AllButLast3Words(InputData As Variant) As String

and

Function Last3Words(InputData As Variant) As String
 
Back
Top