0

[I have more than 47K Full Address Data of different countries and I want to split them into Address, City, State, Zip Code, Country. I have tried many ways but couldn't work any formula as these addresses are different in structure and pattern N.B: I Don't have good knowledge about Excel VBA or Macro]

Robson
  • 2,008
  • 2
  • 7
  • 26
MASBAUR RAHMAN
  • 29
  • 4
  • 1
    Please update this question with specific examples of inputs and desired outputs – Gary's Student Aug 09 '21 at 00:32
  • 3
    different countries have different conventions for addresses. If you can establish a rule with simple words that defines how to split the data and that applies to ALL your data, then that can be built in Excel. If, however, and much more likely, you need a human intelligence to identify if a part of the address belongs to street or to town or to whatever, then you will not be able to parse this data in Excel. So, before asking for a formula, work out the rules to be applied. – teylyn Aug 09 '21 at 00:55
  • 2
    Your France data has the zip code in front of the city separated by a space. The SA data has the zip code after the city, separated by a comma. This is hard to clean up. – teylyn Aug 09 '21 at 00:57
  • I have uploaded another Picture with specific examples of inputs and desired outputs – MASBAUR RAHMAN Aug 09 '21 at 07:59
  • Does this answer your question? [Splitting address with various delimiters into street address, city, state, zip and country](https://stackoverflow.com/questions/64338496/splitting-address-with-various-delimiters-into-street-address-city-state-zip) – T.M. Aug 09 '21 at 09:04
  • @T.M. Thank you so much But I have to use it then I can say anything about it I Never use macro and VBA that's why I need time to check it Thanks again – MASBAUR RAHMAN Aug 09 '21 at 10:57

1 Answers1

0

This task is too complicated to perform with a simple formula, you'll need VBA to do it, let me give you some guidance:

  • You can count the amount of commas in order to guess the content (apparently some addresses start with the name of the building). In case the name of the building is not there, just add a comma in order to have the same format everywhere.
  • Once everything has a similar format (the amount of commas is equal everywhere) you can start splitting, based on the comma as a separator. The results will be "Name", "Full street name and number", "Full city ID", ...
  • Things which are still composed of different items (like "Full city ID") can be split by taking the first part (which is a number, separated from the rest by a space) and the second (the rest of the "Full city ID").

Edit: add small macro

This macro contains the functions Split() and IsNumeric(), it's all you need:

Sub test()
Dim A, B As Integer
T = Split("1, 2, X", ",")
If IsNumeric(T(0)) Then A = T(0) Else A = -1
If IsNumeric(T(2)) Then B = T(2) Else B = -1

MsgBox "Result : A=[" & CStr(A) & "], B=[" & CStr(B) & "]"
End Sub
Dominique
  • 16,450
  • 15
  • 56
  • 112