Seeking a string function (1 Viewer)

kupe · Aug 29, 2003

Dear MasterCraftsmen

Is there a string function that would allow me to divide a sentence into words, and export each word into adjoining cells in an Excel spreadsheet please?

As in:

dividing “fishing expeditions in china”

to “expeditions”, “fishing”, “in”, “china”

Cheers

Mile-O · Aug 29, 2003

What version of Access are you using?

kupe · Aug 29, 2003

I usually put 8-9 on my exports. Sorry, just looked it up. 9.

Mile-O · Aug 29, 2003

Access 2000, then.

You should have the Replace() function.

Pass your string to this function, replacing the spaces with a comma.

I've never seen the Replace function being used as I use Access 97 at the moment.

kupe · Aug 29, 2003

Sorry, M-o-P, yes 2000. Many thanks.

kupe · Aug 29, 2003

Risking the wrath of Mile-O-Phile, I confess my VBA help in access won't tell me about Replace().

To explain the need a little more: I have a sentence in a field that I want to divide into separate words - each single word going into a separate field, and to export the words to Excel, each to be in its own cell.

Cheers

namliam · Aug 29, 2003

What version off access are you using? Replace is a reletively new function to Xp (2002) en i think 2000

If your using an earlier function then try this function:

Code:

Function myreplace(sentance As String) As String
    Dim I As Integer
    For I = 1 To Len(sentance)
        If Mid(sentance, I, 1) = " " Then
'            myreplace = myreplace & "," 'for exporting to a comma seperated file
            myreplace = myreplace & vbTab 'for exporting to a tab seperated(excel) file
        Else
            myreplace = myreplace & Mid(sentance, I, 1)
        End If
    Next I
        
End Function

Regards

kupe · Aug 29, 2003

That looks the stuff, namliam. Many thanks. Off to try it now.

Mile-O · Aug 29, 2003

As an aside, Excel supports the Text To Columns feature where you can split a sentence up by using the option from the menu:

DATA -> TextToColumns

You could then put the space in as the delimiter.

Uncle Gizmo · Aug 30, 2003

Look don’t laugh at this code. It was an early project when I was at the bottom of the learning curve!

There are a couple of functions that you should be able to adapt to do what you want. Any problems with these function let me know and I’ll try to remember how they work.

kupe · Sep 4, 2003

Thanks very much, Tony, that looks really helpful. I look forward to lingering over that later. Amazing day here in the Midlands. Hope you're enjoying it in Berks. Cheers.

Fizzio · Sep 4, 2003

Have a look at this I created a while back. It has some functions and code that you may be able to tailor to your own needs.

kupe · Sep 4, 2003

Good to hear from you, Fizzio. Thanks very much for that. It looks really good. When I get a moment, I'll look closely. Cheers

Uncle Gizmo · Sep 4, 2003

Fizzio "Chief Torturer"

You are torturing me now, I'm jealous of your coding skills

Fornatian · Sep 4, 2003

"If I have been able to see further, it was only because I stood on the shoulders of giants" - Sir Isaac Newton

Fizzio · Sep 5, 2003

Tony Hine said:
Fizzio "Chief Torturer"

You are torturing me now, I'm jealous of your coding skills

Thanks.

The wisest mind has something yet to learn.
George Santayana (1863 - 1952)

kupe · Sep 5, 2003

You are not alone, Tony Hine. Me, too. Stay with the Forum, Fizzio. Please.

Fizzio · Sep 5, 2003

Cheers Guys

I'm not going anywhere soon

Seeking a string function (1 Viewer)

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

Back once again...

Nifty Access Guy

Attachments

Registered User.

Chief Torturer

Attachments

Registered User.

Nifty Access Guy

Dim Person

Chief Torturer

Registered User.

Chief Torturer

Similar threads

Users who are viewing this thread