Splitting text 1

[BSando]

Hi,

I want to split text from a table feild into 3 query fields for manipulation later.

The text looks something like:

1234 southside project X
1324 South bank Project Y

And \i want it to look something like this:

Site ID site name project
----------------------------------
1234 Southside Project X
1324 South bank Project Y

Does anyone know the best way of doing this. I can split the site number from the rest of the text but that's about it. The site name varies as well so searching for blacks can be troublesome.

Any hellp would be appreciated.

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[JBinQLD]

There would need to be some rule here. will [project] field always have the word "project" at start? If so, instr for "project" could help.

You could make a funky form that splits on spaces and allows users to click buttons that shift words left or right into one of three columns and confirm when they're done?

 

[BSando]

thanks.

Looking at the Data again, the project name is always closed in brackets.Does that make it easier separating the data?

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[Remou]

As JBinQLD said, have a look at InStr and Mid.

 

[JBinQLD]

The brackets make it much easier!

    Dim intFirstSpace As Integer
    Dim intFirstBracket As Integer
    Dim strToProcess As String
    
    Dim lngSiteID As Long
    Dim strSiteName As String
    Dim strProject As String
    
    strToProcess = "1324 South bank (Project Y)"
    
    intFirstSpace = InStr(strToProcess, " ")
    intFirstBracket = InStr(strToProcess, "(")
    
    lngSiteID = CLng(Left(strToProcess, intFirstSpace))
    strSiteName = Trim(Mid(strToProcess, intFirstSpace, (intFirstBracket - intFirstSpace)))
    strProject = Mid(strToProcess, intFirstBracket)
    
    MsgBox (lngSiteID & "-" & strSiteName & "-" & strProject)

HTH, JB

 

[BSando]

JbinQLD

Thank yu for your help this s just what I needed.

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[Remou]

BSando
A very good way to thank someone on Tek-Tips is to click on the link above that says:

Thank JBinQLD
for this valuable post!