Hello,

’There is no file attached. Do check out these articles, they might give you the needed start.

https://www.myonlinetraininghub.com/excel-mid-function
https://www.myonlinetraininghub.com/text-to-columns-with-an-excel-formula
https://www.myonlinetraininghub.com/extracting-text-strings-using-excels-text-to-columns

Hi Eskilstuna, 

Here is the file, I apologize, I thought it was uploaded.  Can you take another look at it?  Thank you.  Joseph

Have the text split at 2 different seperators. And this in 2 steps.

The first seperation would 'text to column' by splitting on the comma

Than see the result and what it will show is the State with ZIP code is now in the adjacent cell

When looking at the remainder of the text there appears a number (house number) which is placed after the name

Here you can replace the blank between the Sur Name and the Number with a comma

The position of the above mentioned blank between Sur Name and House Number needs to be located ( n th position )

=(MIN(FIND.ALL({0;1;2;3;4;5;6;7;8;9};A13&"0123456789"))) [ in column "C"]  

Now the formula gives you a position, say with Marcia Jane Parker, that would be 20. Reduce this by 1  (i.e.  +A13-1) [in column "D"]

The last formula would give you the text with a comma, which can enable the result to be seperated by text to column

=REPLACE(A13,D13,1,",")

You now can tidy the string into the correct sequence. (Or combine the formulaes into 1 lengthy formulae)

Hello Joseph,

I notice that you have different delimeters, spaces and commas. That makes it difficult to maintain text such as street adress etc. If it is possible to have one and same delimiter, for example a comma, it will be a lot easier to get things correct.

But you have come to a good start in your sample file. Just shout out if there is anything specific you need assistance with.

Br,

Anders

Hi Anders,

Thanks for responding.  I got another piece of my project which was extracting the street number and name.  However, I do have a question regarding the start number and number of characters.  I have a long list of names so I was wondering if there is a way to do that differently.  In the last name I had to adjust each start number and number of characters.  That would take too long to do. Any suggestions?

Also, I'm stuck on getting the city and state out.  I put the delimeter the same to separate as a hyphen.   Thanks for you help....

Hi Joseph

When splitting names and addresses, is very difficult to get them 100% correct due to the different formats.

I have attempted to split them using some confusing formulas but will work provided the full address follows a certain format (refer attachment)

Hope this will give you some ideas.

Good luck.

Sunny

Hi Sunny,

Thanks for responding to my request.  Yes, that is a little bit confusing, I'm not sure how that all works.  Will those formulas work if I follow your suggestions for another spreadsheet?    Thanks, Joe.

Hi Joseph

I don't see any problem with that. You only need to change the range.

In most conversions, I would be extremely happy to get 80% correct.

As for the other 20%, I will segregate and convert them using other methods,formulas or manually depending on their formats. 

Let us know if you need any help.

Sunny