Separating text in a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin Chapple - 09 Apr 2005 00:03 GMT

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

Reply to this Message

PC Datasheet - 09 Apr 2005 01:33 GMT

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> I have been supplied with data with the address in one field separated
> by commas:
[quoted text clipped - 11 lines]
>
> Robin Chapple

Reply to this Message

Joe Allison - 09 Apr 2005 02:05 GMT

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

> 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
[quoted text clipped - 23 lines]
>>
>> Robin Chapple

Reply to this Message

PC Datasheet - 09 Apr 2005 04:30 GMT

You don't need to do the tab thing. Excel will do the same thing with
commas!

Steve
PC Datasheet

> 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
[quoted text clipped - 31 lines]
> >>
> >> Robin Chapple

Reply to this Message

Robin Chapple - 09 Apr 2005 04:48 GMT

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.

>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.

Reply to this Message

BruceM - 12 Apr 2005 13:21 GMT

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.

> 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
[quoted text clipped - 5 lines]
> >the file and your Access table would automatically bring in each value
> >separately.

Reply to this Message

Robin Chapple - 12 Apr 2005 20:23 GMT

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.

>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
[quoted text clipped - 11 lines]
>> >the file and your Access table would automatically bring in each value
>> >separately.

Reply to this Message

fredg - 09 Apr 2005 02:08 GMT

> I have been supplied with data with the address in one field separated
> by commas:
[quoted text clipped - 11 lines]
>
> Robin Chapple

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.

Signature

Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Reply to this Message

Robin Chapple - 09 Apr 2005 04:45 GMT

I have Access 2002 SP3

>What version of Access?
>If your version supports the Split() function, copy and paste the
[quoted text clipped - 19 lines]
>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:
[quoted text clipped - 11 lines]
>>
>> Robin Chapple

Reply to this Message

John Nurick - 09 Apr 2005 08:04 GMT

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:
[quoted text clipped - 11 lines]
>
>Robin Chapple

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Robin Chapple - 10 Apr 2005 07:12 GMT

Thanks John,

I'll let you know how it goes.

>Hi Robin,
>
[quoted text clipped - 39 lines]
>>
>>Robin Chapple

Reply to this Message