Splitting Number & Text

  • Thread starter Thread starter Graham Clements
  • Start date Start date
G

Graham Clements

I have a field which consists of both text and number,
that is a place name and their contact number, ie London
020 456 3423. What I would like to achieve is to append
the place name to a place name field and the contact
number to a contact number field. To add to this the
data in this field is quite messy, for example:

TTTTTT NNN NNN NNNNN (Text & Number)
TTTTT TTTTTTNNNNNNNNNNN (Text & Number)
TTTT.NNN NNNNNNNN (Text & Number with full stop)
TTTTTTT TTT (Just Text)
NNNNNN (Just Number)

The only patterns I have noticed are as follows:

If there is text and number the text will always come
first.
If there is text and number there is always 11 digits
(although there can be spaces between).
If there is just number then there is always 6 digits.

So that is the problem, I understand this is very
complicated and has proved difficult for me to explain so
if any further information is requested let me know.

Thankyou for any help in advance.

Graham
 
Let's try a sub a see what we get.

Public Function SplitMe(varInput As Variant) As Variant
Dim intLength As Integer, i as Integer
Dim strOutput As String, Dim lngOutput As Long
intLength = Nz(Len(varInput), 0)
If intLength = 0 then
SplitMe = Null
Exit Function
ElseIf IsNumeric(varInput) Then
'include the comma in the output
SplitMe = "," & varInput
Exit Function
End If
For i = 1 To intLength
If IsNumeric(Right(varInput, intLength - i)) Then
strOutput = Left(varInput, i)
lngOutput = Right(varInput, intLength - i)
'add a comma to the output
SplitMe = strOutput & "," & lngOutput
Exit Function
End If
Next i
'If we get this far we didn't find a number so
'just return the text and the comma
SplitMe = varInput & ","
End Function

Now for the code that calls this function, in the query you would call this
function in the Update To part of an update query where what you are
updating are the 2 fields you mentioned. This could also be done in a append
query if that is what you're doing. This is not going to be efficient as far
as speed. I'm using a comma as a delimiter. Change it here and in the
function to a character that won't be in your input data.

Text value:
IIF(Not IsNumeric(Left([InputValue], InStr(Nz(SplitMe([InputValue]), ","),
",") - 1)), Left([InputValue], InStr(Nz(SplitMe([InputValue]), ","), ",") -
1), Null)

Number value:
IIF(IsNumeric(Right([InputValue], InStr(Nz(SplitMe([InputValue]), ","),
",") - 1)), Right([InputValue], InStr(Nz(SplitMe([InputValue]), ","), ",") -
1), Null)

I have not tested the IIF statements, so there may be some syntax error in
them.
 
Back
Top