split a field that contains text and numbers into two fields

  • Thread starter Thread starter Tom Hanley
  • Start date Start date
T

Tom Hanley

I have a database with a field that has text and numbers combined. I want to
create two fields with text in on and numbers in the other. the text is
always first and the numbers follow. (i.e. "abcd123").
Thanks,
Tom Hanley
 
Tom,

Genrally speaking, I would go about it buy first creating two new fields in
the tablke, one for the text and one for the numeric part, then using an
update query to populate them, and finally deleting the original field.
The question here is how you break up your original field: how do you tell
Access whcih part is text and which part is numkeric? For example, is the
length of at leat one part constant?

HTH,
Nikos
 
Yes, I have gotten to the part of created the two fields but I want to
specify a range when using the left and instr functions. For example, I can
convert all the data containing the number "1" in the string by using:
Left([trees]![FB_DESC],InStr([trees]![FB_DESC],"1")-1)
However, if the number "1" isn't found I get nothing and I still need to
test for all the first numbers instr could encounter between 0-9.
 
Instead of searching for a specific number in the text field, I want to test
for the first character that is recognized as a number in the text field...

Tom Hanley said:
Yes, I have gotten to the part of created the two fields but I want to
specify a range when using the left and instr functions. For example, I can
convert all the data containing the number "1" in the string by using:
Left([trees]![FB_DESC],InStr([trees]![FB_DESC],"1")-1)
However, if the number "1" isn't found I get nothing and I still need to
test for all the first numbers instr could encounter between 0-9.



Nikos Yannacopoulos said:
Tom,

Genrally speaking, I would go about it buy first creating two new fields in
the tablke, one for the text and one for the numeric part, then using an
update query to populate them, and finally deleting the original field.
The question here is how you break up your original field: how do you tell
Access whcih part is text and which part is numkeric? For example, is the
length of at leat one part constant?

HTH,
Nikos

want
Click to expand...
Click to expand...
 
Tom Hanley said:
Instead of searching for a specific number in the text field, I want to test
for the first character that is recognized as a number in the text
Click to expand...
field...

Using the IsNumeric function something like:

dim i as integer
dim partStr as string
dim partNum as string

For i = 1 to Len(YourTextField)
If IsNumeric(Mid$(YourTextField, i, 1) Then
partStr = Left$(YourTextField, i -1)
partNum = Mid$(YourTextField, i)
Exit For
End If
Next
 
Back
Top