Insert a Line-Feed Between Every 3rd Character Using an Excel Worksheet Formula

 

Per an Oz Du Soleil post on how to separate 5 area codes in a single 15 character string with line breaks using Power Query

https://m.youtube.com/watch?v=yorGlCrfqY0  

here is a way to do the same thing using a worksheet formula.

With the string in cell A1. Enter the following array formula in a cell.

=TEXTJOIN(CHAR(10),,MID(A1,{1,4,7,10,13},3))

Make sure that you select word wrap enabled for the cell containing the formula.

2 thoughts on “Insert a Line-Feed Between Every 3rd Character Using an Excel Worksheet Formula

  1. Kevin Lehrbass

    Interesting….I also had the textjoin & char(10) idea and created the exact same formula! I’m still discovering amazing ways to use textjoin. And it’s good to have more solution options with Power Query…

    Reply
  2. Pingback: Stack Groups of 3 Numbers in Excel | My Spreadsheet Lab

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s