Format string

[bmquiroz]

Hi all,

I need some help with formatting strings using either regexp (preferably) or VB functions Trim, InStr, etc. I've been trying to use regexp but have run into a wall trying to find a working expression.

Here is what I need to do:

Joan R. Platt convert to -> Joan|R|Platt

Lehman Business Trust convert to -> Lehman|Business|Trust

I need to (1) replace white space with pipe symbol "|" (2) remove any commas or periods found in the string (3) remove LLC or Inc if found in the string.

Any help is appreciated.

-B

 

[three57m]

If you are using VB6 and Not VB5 then you could find the Replace function in vb6 helpful.

The Replace function will replace ALL occurences you specify with whatever you want, not just the first occurence

I dont have vb on my machine but i think it would look something like this to get rid of LLC

Replace MyString, "LLC", WithThisString

 

[dglienna]

I created a few functions that should work like you want.
Feel free to tweak it if you need to.

Private Sub Form_Load()
  Dim t$
  t = "John C. Doe"
  MsgBox parse(t)
End Sub

Function parse(x As String) As String
  Dim d As Integer, w$, temp$, arr() As String, z As Integer
  arr() = Split(Trim$(x), " ")
  For z = 0 To UBound(arr)
    arr(z) = Trim$(arr(z))
    arr(z) = Replace$(arr(z), "LLC", "")
    arr(z) = Replace$(arr(z), "Inc", "")
    For d = 1 To Len(arr(z))
       If Valid(Mid$(arr(z), d, 1)) = True Then
          w = w & Mid$(arr(z), d, 1)
        End If
    Next d
    parse = parse & w & "|"
    w = ""
  Next z
  parse = Left$(parse, Len(parse) - 1)
End Function

Function Valid(ltr$) As Boolean
  Valid = True
  Select Case ltr
  Case ".", ",", " "
    Valid = False
  End Select
End Function

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[strongm]

Option Explicit

Private Sub Command1_Click()
Dim re As RegExp
Dim result As String

Set re = New RegExp
re.Global = True
re.Pattern = ",|\.|(\s+LLC)|(\s+Inc)|(\s+$)"
result = re.Replace("Joan R. Platt ", "")
re.Pattern = "\s+"
Debug.Print re.Replace(result, "|")
End Sub

 

[bmquiroz]

You guys are great!

Thanks!!

 

[vbSun]

str1 = Replace(Trim(Replace(Replace(Replace(Replace(strsource, ",", ""), ".", ""), "LLC", ""), "Inc", "")), " ", "|")

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[strongm]

The OP says

>white space

not just space ... so, for example, you need to deal with multiple spaces and tabs

 

[vbSun]

One line way won't work then!

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[bmquiroz]

Guys thanks again for your help.

strongm, is there any way of removing the middle initial from name strings using regex? I guess an expression that would look for a period then remove the letter before it?

Thanks.

 

[bmquiroz]

I'll need it to remove any prefix or suffix in the name as well.

Thanks again for your help

 

[johnwm]

Probably best you look at

http://msdn.microsoft.com/library/d...html/42add7d6-4490-4d51-a5a7-5d654a317b07.asp

which will save you having to ask each time you need a small change

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[strongm]

Sure. Just a minor modification of the pattern:

[blue]Option Explicit

Private Sub Command1_Click()
    Dim re As RegExp
    Dim result As String
    
    Set re = New RegExp
    re.Global = True
    re.Pattern = ",|\.|(\s+LLC)|(\s+Inc)|(\s+$)|(\s+\S\.)|(Mr |Mrs |Ms | MD)"
    result = re.Replace("Ms Joan R. Platt MD ", "")
    re.Pattern = "\s+"
    Debug.Print re.Replace(result, "|")
End Sub[/blue]

 

[bmquiroz]

strongm you are awesome, thanks!