Add Formating Info to words/numbers in a field

Jonathan Lueb

New member
Local time
Today, 12:32
Joined
Mar 25, 2007
Messages
9
I need to evaluate a field that contains a sentence that has words and numbers (codes) that need formatting codes added.

Example:
In the beginning 7225 God 430 created the heavens 8064 and the earth 776.

Needs to become:

In the beginning{\super\cf6 7225} God{\super\cf6 430} created the heavens{\super\cf6 8064} and the earth{\super\cf6 776} .

So I need to find the numbers and then add the codes to the numbers and reinsert them into the field. I then will go to the next record and repeat the process until I reach the end of the file. I know this should be very simple for the experienced. I know I have done something simular in the past but do not program enough to keep my skills up. If someone could help me along with this I would greatly appreciate it!
 
Jonathon,

You need something like:

Code:
Public Function fnFormatString(strInput As String) As String
Dim varCells As Variant
Dim i As Long
Dim strTemp As String

strTemp = ""

varCells = Split(strInput, " ")
For i = 0 To UBound(varCells) - 1
   If IsNUmber(varCells(i) Then
      strTemp = strTemp & "{\super\cf6 " & varCells(i) & "} "
   Else
      strTemp = strTemp & varCells(i) & " "
   End If
   Next i

fnFormatString = strTemp

End Function

Then you can call it in a query:

FormattedString: fnFormatString([YourString])

hth,
Wayne
 
Wayne,

Thanks a lot that was exactly what I needed. I knew it was simple! I needed to change IsNumber to IsNumeric but otherwise it worked great I can expand on that to make other formatting changes in the text which is what I needed.

Thanks for the incredibly quick reply and the help!!!

Jonathan
 
Jonathon,

Glad to help. Sorry about the IsNumeric thing.

Wayne
 
Jonathon,

If you have to compare the cells against entries in a table, use DCount.

If DCount("[TheField]", "YourTable", "[TheField] = '" & varCells(i) & "'") > 0 ...

This assumes that the entries are strings (i.e. the single-quotes added).

Wayne
 
Thanks Wayne!

I've got my code up to this point.

Public Function fnFormatString(strInput As String) As String
Dim varCells As Variant
Dim SearchCharacterSet As Variant
Dim i As Long
Dim c As Long
Dim strTemp As String
Dim strVar1 As String
Dim strVar2 As String
Dim SearchChar As String

strTemp = ""
SearchCharacterSet = ".,:;!?'" ''& Chr(34) ]})

For c = 1 To Len(SearchCharacterSet)
SearchChar = Mid(SearchCharacterSet, c, 1)

CarPos = InStr(strInput, SearchChar)
If Not Mid(strInput, CarPos + 1, 1) = " " Then
strVar1 = Mid(strInput, 1, CarPos)
strVar2 = Mid(strInput, CarPos + 1, Len(strInput))
strInput = Trim(strVar1 & " " & strVar2)
End If
Next c

strVar1 = ""
strVar2 = ""

varCells = Split(strInput, " ")
For i = 0 To UBound(varCells) - 1
For c = 1 To Len(SearchCharacterSet)
SearchChar = Mid(SearchCharacterSet, c, 1)
If Right(varCells(i), 1) = SearchChar Then
strVar1 = Left(varCells(i), Len(varCells(i)) - 1)
strVar2 = Right(varCells(i), 1)
Exit For
Else
strVar1 = varCells(i)
strVar2 = ""
End If
Next c
If IsNumeric(strVar1) Then
strTemp = strTemp & "{\super\cf6 " & strVar1 & "}" & strVar2 & " "
ElseIf DCount("[Topic]", "TVM", "[Topic] = '" & strVar1 & "'") > 0 Then
strTemp = strTemp & "{\super\cf2 " & strVar1 & "}" & strVar2 & " "
Else
strTemp = strTemp & strVar1 & " "
End If
Next i

fnFormatString = strTemp

End Function

It works well except that in the top portion it only finds the first occurance of the Search Character.

I know I could figure it out but you are obviously much much more experienced then I and can probably give me the answer off the top of your head. You have already taken me in a different direection than I had originally been thinking and I think this is more efficient. At least it takes a lot less coding than I had originaly been working with.

I think I need to determine the number of occurances in the string I want to search and then loop through them evaluating and correcting where necessary. I have had to add this extra coding because I find that the original text at times lacks a space between the punctuation and the next word. So basically I am trying to guarantee that there is a space there. Maybe you know of a more efficient way of doing that. In the second portion I had to make sure that the punctuation was separated from the string I was comparing with my other list. I also had to move my brackets out of my Search Character Set because they were goofying things up.

Do you have any suggestions?

Jonathan
 
Ok Wayne,

I've solved that issue, on my own, with the following code:

For c = 1 To Len(SearchCharacterSet)
SearchChar = Mid(SearchCharacterSet, c, 1)
CharCount = UBound(Split(strInput, SearchChar))
For d = 1 To CharCount
CarPos = InStr(strInput, SearchChar)
If Not Mid(strInput, CarPos + 1, 1) = " " Then
strVar1 = Mid(strInput, 1, CarPos)
strVar2 = Mid(strInput, CarPos + 1, Len(strInput))
strInput = Trim(strVar1 & " " & strVar2)
End If
Next d
Next c

How dumb of me! You already showed me how to do that. However I still have a small problem.
When I compare the strings it finds with what's in my list in a separate table it finds words lower case which I don't want it to find.

My list is all caps the codes in the text are all caps.
Such as: A-DPM-C
One is : HEB
If the string it finds in the text is "Heb" I don't want it to format that because it is not mean to be a code. How can I make it case sensetive?

Jonathan
 
I think I've solved just about all my issues. The only thing left is how to deal with words with appostophies such as "Mary's". If anybody has any suggestions I'd appriciate it.

Here is the code as it stands.

Code:
Public Function fnFormatString(strInput As String) As String
Dim varCells As Variant
Dim SearchCharacterSet As Variant
Dim i As Long, c As Long, d As Long
Dim strTemp As String, strVar1 As String, strVar2 As String
Dim SearchChar As String
Dim CharCount As Integer
Dim varX As Variant, test As Variant


strTemp = ""
SearchCharacterSet = ".,:;!?"      ''& Chr(34) ]})'
   
For c = 1 To Len(SearchCharacterSet)
    SearchChar = Mid(SearchCharacterSet, c, 1)
    CharCount = UBound(Split(strInput, SearchChar))
    For d = 1 To CharCount
        CarPos = InStr(strInput, SearchChar)
        If Not Mid(strInput, CarPos + 1, 1) = " " Then
            strVar1 = Mid(strInput, 1, CarPos)
            strVar2 = Mid(strInput, CarPos + 1, Len(strInput))
            strInput = Trim(strVar1 & " " & strVar2)
        End If
    Next d
Next c

strVar1 = ""
strVar2 = ""

varCells = Split(strInput, " ")
For i = 0 To UBound(varCells) - 1
    For c = 1 To Len(SearchCharacterSet)
        SearchChar = Mid(SearchCharacterSet, c, 1)
        If Right(varCells(i), 1) = SearchChar Then
            strVar1 = Left(varCells(i), Len(varCells(i)) - 1)
            strVar2 = Right(varCells(i), 1)
            Exit For
        Else
            strVar1 = varCells(i)
            strVar2 = ""
        End If
    Next c
   If IsNumeric(strVar1) Then
      strTemp = strTemp & "{\super\cf6 " & strVar1 & "}" & strVar2 & " "
   ElseIf DCount("[Topic]", "TVM_", "[Topic] = '" & strVar1 & "'") > 0 Then
     varX = DLookup("[Topic]", "TVM_", "[Topic] = '" & strVar1 & "'")
      ' test = StrComp(varX, strVar1, 0)
      If StrComp(varX, strVar1, 0) = 0 Then
        strTemp = strTemp & "{\super\cf2 " & strVar1 & "}" & strVar2 & " "
      Else
        strTemp = strTemp & strVar1 & " "
      End If
   Else
      strTemp = strTemp & strVar1 & " "
   End If
Next i

fnFormatString = strTemp

End Function

Wayne, thanks for your help!

Jonathan
 

Users who are viewing this thread

Back
Top Bottom