Text Split Want To Create Macro

C

Chetan Kumar

Registered User.
Local time
, 19:42
Joined
Mar 21, 2013
Messages
16
Hi ,

My self Chetan from india please help me to solved my query.......
I'm a Business Analyst facing problem regarding split text data .....
For Exp.
A & A Adeniran (403) 460-2799 153 Somerset Way SW Calgary AB T2Y 3K3
I Want Name, Phone No, Address, City and Zip Code
 
Namaste Chetan, Welcome to AWF.. :)

This would require more than a simple Split function to do what you are trying to do here.. It would involve, Mid, InStr, Len or several of the String Manipulation functions available in VBA.. even then it might be way too complicated as if there is some Data missing then the logic is thrown out of the window..

I know this might not be the answer you are looking for, but if you are confident that all data will be entered.. We can work something out..
 
Hi pr2-eugin ,

could you please share exp. for the same .:confused:
 
So what is the City in the String ??

A & A Adeniran (403) 460-2799 153 Somerset Way SW Calgary AB T2Y 3K3
 
Some time data something like that.......
A & A De Vera (905) 495-9939 61 Baha Cres Brampton ON L7A 2J4
A & C Wagar (905) 430-1318 41 Scadding Ave Whitby ON L1N 9X4
A & J Pender (905) 735-7098 142 Muirfield Trail Welland ON L3B 6G7
A & K Zeller (905) 765-3996 53 Harrison Rd Caledonia ON N3W 2J8
A & M Kujala (519) 243-2997 9748 Lakeshore Rd Grand Bend ON N0M 1T0
 
Reply for Post #5, See that exactly where the problem is.. If you do not need the city, but just One line for address and the postcode then you are good to go (atleast for this sample data).. This logic will fail for any other data that does not fit the criteria..

Just looking at Post #6, is that One long String or each of them in a separate entity?
 
Chetan, I have written the function to extract the values from the Long String and place them on an Array which you can use to manipulate.. Again, this function will not work on all values.. but based on the test data it works.. Hope this helps (to some extent)
Code: 
Public Function splitLongString(tmpStr As String) As String()
[COLOR=Green]'-----------------------------------------------------------------------
'   A Function that will take in a Long String, and returns
'   an Array each entity holds a dissected parts of the String.
'
'   Input   : A Normal String, that needs to be broken down/split
'   Output  : An array of Strings.
'   Example :
'       Dim tmpArr() As String
'       tmpArr = splitLongString("A & A Adeniran (403) " & _
'                   "460-2799 153 Somerset Way SW Calgary AB T2Y 3K3")
'   Author:    Paul Eugin.
'-----------------------------------------------------------------------[/COLOR]
    Dim retArr(5) As String, varStr As String, i As Integer
    tmpStr = Trim(tmpStr)
    
    retArr(0) = Left(tmpStr, InStr(tmpStr, "(") - 1)
    retArr(1) = Mid(tmpStr, Len(retArr(0)) + 1, 15)
    
    varStr = Mid(StrReverse(tmpStr), Len(Left(StrReverse(tmpStr), 12)))
    retArr(3) = StrReverse(Left(varStr, InStr(varStr, " ")))
    
    retArr(2) = Mid(tmpStr, Len(retArr(0)) + Len(retArr(1)))
    retArr(2) = Mid(retArr(2), 1, InStr(retArr(2), retArr(3)))
    
    retArr(4) = Right(tmpStr, 7)
    
    For i = 0 To 4
        retArr(i) = Trim(retArr(i))
    Next
    
    splitLongString = retArr
End Function
To test the function from immediate window..
Code: 
Public Sub testAWF_Chetan()
    Dim tmpArr() As String, i As Integer
    tmpArr = splitLongString("A & M Kujala (519) 243-2997 9748 Lakeshore Rd Grand Bend ON N0M 1T0 ")
    For i = 0 To UBound(tmpArr)
        Debug.Print tmpArr(i)
    Next
End Sub
Would return the result as..
Code: 
A & M Kujala
(519) 243-2997
9748 Lakeshore Rd Grand
Bend
N0M 1T0
 
pr2-eugin said:
Chetan, I have written the function to extract the values from the Long String and place them on an Array which you can use to manipulate.. Again, this function will not work on all values.. but based on the test data it works.. Hope this helps (to some extent)
Code: 
Public Function splitLongString(tmpStr As String) As String()
[COLOR=Green]'-----------------------------------------------------------------------
'   A Function that will take in a Long String, and returns
'   an Array each entity holds a dissected parts of the String.
'
'   Input   : A Normal String, that needs to be broken down/split
'   Output  : An array of Strings.
'   Example :
'       Dim tmpArr() As String
'       tmpArr = splitLongString("A & A Adeniran (403) " & _
'                   "460-2799 153 Somerset Way SW Calgary AB T2Y 3K3")
'   Author:    Paul Eugin.
'-----------------------------------------------------------------------[/COLOR]
    Dim retArr(5) As String, varStr As String, i As Integer
    tmpStr = Trim(tmpStr)
    
    retArr(0) = Left(tmpStr, InStr(tmpStr, "(") - 1)
    retArr(1) = Mid(tmpStr, Len(retArr(0)) + 1, 15)
    
    varStr = Mid(StrReverse(tmpStr), Len(Left(StrReverse(tmpStr), 12)))
    retArr(3) = StrReverse(Left(varStr, InStr(varStr, " ")))
    
    retArr(2) = Mid(tmpStr, Len(retArr(0)) + Len(retArr(1)))
    retArr(2) = Mid(retArr(2), 1, InStr(retArr(2), retArr(3)))
    
    retArr(4) = Right(tmpStr, 7)
    
    For i = 0 To 4
        retArr(i) = Trim(retArr(i))
    Next
    
    splitLongString = retArr
End Function
To test the function from immediate window..
Code: 
Public Sub testAWF_Chetan()
    Dim tmpArr() As String, i As Integer
    tmpArr = splitLongString("A & M Kujala (519) 243-2997 9748 Lakeshore Rd Grand Bend ON N0M 1T0 ")
    For i = 0 To UBound(tmpArr)
        Debug.Print tmpArr(i)
    Next
End Sub
Would return the result as..
Code: 
A & M Kujala
(519) 243-2997
9748 Lakeshore Rd Grand
Bend
N0M 1T0
Click to expand...



Thanku Very Much......
I'll try that function then tell u further action....Thanks
 
I use A2010, it works fine.. All functions I have used are basic String functions, which I hope are available in previous versions as well.. Did you try it?
 
pr2-eugin said:
I use A2010, it works fine.. All functions I have used are basic String functions, which I hope are available in previous versions as well.. Did you try it?
Click to expand...



Yes I tried its working very well.. thanks thank you very much.....:)
 
Hi Paul good morning,
Could you please help me about my query?
I have one access file with contain that kind of data…
How can I split that file first field which contains data? In same table next fields without macro ….:confused:
Please help me out Plz
Chetan Kumar Raghuwanshi
 
Okay, this is somewhat closer of what you wish to accomplish.. Same process except instead of returning an Array of Strings, it would return a String based on the position/entity you wish to have..
Code: 
Public Function splitLongString(tmpStr As String, tmpPosition) As String
[COLOR=Green]'-----------------------------------------------------------------------
'   A Function that will take in a Long String, and returns
'   a String which is a dissected part of the String, based on the
'   location that it is required of.
'
'   Input   : A Normal String, that needs to be broken down/split
'             1 - Name
'             2 - Telephone Number
'             3 - Address
'             4 - City
'             5 - Postcode
'   Output  : A String based the location number.
'   Example :
'       ? splitLongString("A & A Adeniran (403) " & _
'                   "460-2799 153 Somerset Way SW Calgary AB T2Y 3K3", 2)
'         (403) 460-2799
'       telNumber : splitLongString([fieldName], 2)
'
'   Author  :    Paul Eugin, Mar 2013
'-----------------------------------------------------------------------[/COLOR]
    Dim varStr As String, dumVarStr As String
    tmpStr = Trim(tmpStr)
    
    Select Case tmpPosition
        Case 1
            splitLongString = Trim(Left(tmpStr, InStr(tmpStr, "(") - 1))
        Case 2
            splitLongString = Trim(Mid(tmpStr, InStr(tmpStr, "("), 15))
        Case 3
            dumVarStr = Mid(StrReverse(tmpStr), Len(Left(StrReverse(tmpStr), 12)))
            dumVarStr = StrReverse(Left(dumVarStr, InStr(dumVarStr, " ")))
            varStr = Mid(tmpStr, Len(Left(tmpStr, InStr(tmpStr, "(") - 1)) + 15)
            splitLongString = Trim(Mid(varStr, 1, InStr(varStr, dumVarStr)))
        Case 4
            varStr = Mid(StrReverse(tmpStr), Len(Left(StrReverse(tmpStr), 12)))
            splitLongString = Trim(StrReverse(Left(varStr, InStr(varStr, " "))))
        Case 5
            splitLongString = Trim(Right(tmpStr, 7))
        Case Else
            splitLongString = "N/A"
    End Select
End Function
Hope this helps..
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom