Split Address with different delimiters into Different columns

[Shreesurya]

Hi Pros,

I have been trying to split address from a cell to multiple columns. but I have to create a step every time I have to split which has different delimiter.

However I would want this to be one step where it identifies different delimiter and split it accordingly to multiple columns.

Input:
17175 Misty Lake Dr
Strongsville, OH 44136

I split this by first using Linefeed and then split by ", " and then with Space. (This actually create 3 steps in Power Query)

Delimiter 1 = Linefeed
Delimiter 2 = ", "
Delimiter 3 = Space

I just want to split everything in one step.

Please help me.

 

[EXCEL MAX]

What should be the output from this example?
Can you tell us what you expect in which column as result?

 

[Shreesurya]

What should be the output from this example?
Can you tell us what you expect in which column as result?

Thanks for the response, Below is the example of what I am trying to achieve.

First Column is input and I am trying to split it into 4 different columns.

Input	Address	City	State	Zip
17175 Misty Lake Dr Strongsville, OH 44136	17175 Misty Lake Dr	Strongsville	OH	44136

 

[EXCEL MAX]

Do you want VBA code or formulas? I prefer code.

 

[Shreesurya]

Do you want VBA code or formulas? I prefer code.

I was trying this with Power query as I get data scrubbed from Web.
Is it possible to put it in one step for Power query. I was able to do it in 4 Steps.

 

[EXCEL MAX]

Sorry, but I can't be helpful about Power Query.