Separating text in a field

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have been supplied with data with the address in one field separated
by commas:

POBox, Suburb, State, Postcode

I need to have each of the values in separate fields.

I have succeeded in separating the POBox field but failed to progress
any further.

Advice welcome.

Thanks,

Robin Chapple
 
You could put the Address in a separte file (text file). Then you could
treat that file as a comma separated values file and use the TransferText
method to import. Each value would import separately. You could also link to
the file and your Access table would automatically bring in each value
separately.
 
I would just open the document in Word, do a find and replace function. I
would find all commas (,) and replace it with a tab (^t). Save it as a .TXT
file. Open it in Excel and when the dialog box asks you for the delimiter,
select 'tab'. All the field headers will be in separate cells.

Joe Allison
Fenton, MI
 
I have been supplied with data with the address in one field separated
by commas:

POBox, Suburb, State, Postcode

I need to have each of the values in separate fields.

I have succeeded in separating the POBox field but failed to progress
any further.

Advice welcome.

Thanks,

Robin Chapple
Click to expand...

What version of Access?
If your version supports the Split() function, copy and paste the
following into a Module.

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(x)

End Function
====

Call it from a query:
POBox:ParseText([Fullfield],0)
Suburb:ParseText([Fullfield],1)
State:ParseText([Fullfield],2)
PostCode:ParseText([Fullfield],3)

See VBA help for all of the arguments available in this new function.

If you have an older version of access post back and I'll dig out that
code.
 
You don't need to do the tab thing. Excel will do the same thing with
commas!

Steve
PC Datasheet
 
I have Access 2002 SP3

What version of Access?
If your version supports the Split() function, copy and paste the
following into a Module.

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(x)

End Function
====

Call it from a query:
POBox:ParseText([Fullfield],0)
Suburb:ParseText([Fullfield],1)
State:ParseText([Fullfield],2)
PostCode:ParseText([Fullfield],3)

See VBA help for all of the arguments available in this new function.

If you have an older version of access post back and I'll dig out that
code.

I have been supplied with data with the address in one field separated
by commas:

POBox, Suburb, State, Postcode

I need to have each of the values in separate fields.

I have succeeded in separating the POBox field but failed to progress
any further.

Advice welcome.

Thanks,

Robin Chapple
Click to expand...
Click to expand...
 
I did not explain fully. These four values are part of a record. There
are twenty other fields and the four values need to remain linked to
the ID.
 
Hi Robin,

I'd use a function like this


Public Function ParseByComma(V As Variant, Pos As Long) As Variant
If IsNull(V) Then
ParseByComma = Null
Else
On Error Resume Next
ParseByComma = Trim(Split(V, ",")(Pos))
If Err.Number <> 0 Then
'Probably there aren't enough fields
Err.Clear
ParseByComma = Null
End If
On Error GoTo 0
End If
End Function

and use it in calculated fields in a query like this (XXX is the
fieldname):

POBox: ParseByComma([XXX],0)

Suburb: ParseByComma([XXX],1)

and so on
 
Thanks John,

I'll let you know how it goes.

Hi Robin,

I'd use a function like this


Public Function ParseByComma(V As Variant, Pos As Long) As Variant
If IsNull(V) Then
ParseByComma = Null
Else
On Error Resume Next
ParseByComma = Trim(Split(V, ",")(Pos))
If Err.Number <> 0 Then
'Probably there aren't enough fields
Err.Clear
ParseByComma = Null
End If
On Error GoTo 0
End If
End Function

and use it in calculated fields in a query like this (XXX is the
fieldname):

POBox: ParseByComma([XXX],0)

Suburb: ParseByComma([XXX],1)

and so on

I have been supplied with data with the address in one field separated
by commas:

POBox, Suburb, State, Postcode

I need to have each of the values in separate fields.

I have succeeded in separating the POBox field but failed to progress
any further.

Advice welcome.

Thanks,

Robin Chapple
Click to expand...
Click to expand...
 
Where are the other twenty fields? Are they also part of a list separated by
commas? I would try saving the text file as a .csv file, which should open
by default in Excel. If it looks OK in Excel you could import it into
Access. I have used this method to import an address book into an Access
table.
 
The problem is in one of twenty one fields where the four subjects are
contained.

For speed I did just that and then imported it.

I will use the other technique when I am not so busy.

Thanks.
 
Back
Top