Split Text into Different Columns - Formulas only - No VBA - or Text to Columns

[Richard2542]

I need a series of formulas to convert a single line of text into 4 columns.

Address:
1927 S Apple Valley Rd., Madison, WI, 34561

Convert To:

Column A2
1927 S Apple Valley Rd.

Column B2
Madison

Column C2
WI

Column D2
34561

 

[shift-del]

Hi

=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(A1,", ","</z><z>")&"</z></y>","//z"))

 

[Marcílio_Lobão]

richard2542, Good evening.

If your data have ALL the same standard, try using these formulas:

A2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ),    1, 100))

B2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 100, 100))

C2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 200, 100))

D2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 300, 100))

Please tell us if it worked for you.

I hope I've helped.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Split text into columns - formula only, no vba or excel Text to Columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Richard2542]

richard2542, Good evening.

If your data have ALL the same standard, try using these formulas:

A2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ),    1, 100))

B2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 100, 100))

C2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 200, 100))

D2 -->

=TRIM( MID( SUBSTITUTE(A1, ",", REPT(" ",100) ), 300, 100))

Please tell us if it worked for you.

I hope I've helped.

Thanks for your help...worked great