Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

String Manipulation - VBA

DertweillerDertweiller Posts: 37Member
Hi guys,

I have been trying to work out how to extract 1st, 2nd, 3rd and 4th addresses from an address that is separated with commas. Example: "No 3 Greengrass Blvd, 104 George King Street, Harlem, USA".

1st = "No 3 Greengrass Blvd"
2nd = "104 George King Street"
3rd = "Harlem"
4th = "USA"

I have managed to use the string extractor function that I have written to count the number of commas in the column and split the address accordingly - so if the address is:

"No 3 Greengrass Blvd, 104 George King Street," - the result would be

1st = "No 3 Greengrass Blvd"
2nd = "104 George King Street"

and if the address is: "No 3 Greengrass Blvd, 104 George King Street, Harlem," - the result would be:

1st = "No 3 Greengrass Blvd"
2nd = "104 George King Street"
3rd = "Harlem"

The problem is if the address is: "No 3 Greengrass Blvd, 104 George King Street" the code does not recognise the string that falls to the right of the first comma as being the 2nd address just because the second, third or fourth comma is missing. Examples:

1: "No 3 Greengrass Blvd, 104 George King Street," Works
2: "No 3 Greengrass Blvd, 104 George King Street" does not work

Please help





[blue]I am Invincible... ;-)[/blue]

Comments

  • HackmanCHackmanC Posts: 441Member
    Did you used the Split$() function ?
    Anyway, write this before call your function, add the "," if it doesnt exists.

    [code]
    strAddress = IIf(Rigth$(strAddress, 1) <> ",", strAddress & ",", strAddress)
    [/code]

    ----
    : Hi guys,
    :
    : I have been trying to work out how to extract 1st, 2nd, 3rd and 4th addresses from an address that is separated with commas. Example: "No 3 Greengrass Blvd, 104 George King Street, Harlem, USA".
    :
    : 1st = "No 3 Greengrass Blvd"
    : 2nd = "104 George King Street"
    : 3rd = "Harlem"
    : 4th = "USA"
    :
    : I have managed to use the string extractor function that I have written to count the number of commas in the column and split the address accordingly - so if the address is:
    :
    : "No 3 Greengrass Blvd, 104 George King Street," - the result would be
    :
    : 1st = "No 3 Greengrass Blvd"
    : 2nd = "104 George King Street"
    :
    : and if the address is: "No 3 Greengrass Blvd, 104 George King Street, Harlem," - the result would be:
    :
    : 1st = "No 3 Greengrass Blvd"
    : 2nd = "104 George King Street"
    : 3rd = "Harlem"
    :
    : The problem is if the address is: "No 3 Greengrass Blvd, 104 George King Street" the code does not recognise the string that falls to the right of the first comma as being the 2nd address just because the second, third or fourth comma is missing. Examples:
    :
    : 1: "No 3 Greengrass Blvd, 104 George King Street," Works
    : 2: "No 3 Greengrass Blvd, 104 George King Street" does not work
    :
    : Please help
    :
    :
    :
    :
    :
    : [blue]I am Invincible... ;-)[/blue]
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]

  • WoFWoF Posts: 3Member
    I should use the split function like that:

    'assume AdrString contains your address

    dim Lines$() 'need a dynamic string array

    Lines = Split(AdrString, ",")

    You can use UBound(Lines) to determine how many resulting strings you got. They are available as Lines(0) up to Lines(n), where n is your UBound value. So if UBound(Lines) is 3, your address line has split into 4 lines.
  • DertweillerDertweiller Posts: 37Member
    [b][red]This message was edited by Dertweiller at 2006-7-6 3:52:44[/red][/b][hr]
    : I should use the split function like that:
    :
    : 'assume AdrString contains your address
    :
    : dim Lines$() 'need a dynamic string array
    :
    : Lines = Split(AdrString, ",")
    :
    : You can use UBound(Lines) to determine how many resulting strings you got. They are available as Lines(0) up to Lines(n), where n is your UBound value. So if UBound(Lines) is 3, your address line has split into 4 lines.
    :

    Thanks to both of you for your intellectual contribution, I have finally managed to get it working. Keep up the good work.


Sign In or Register to comment.