Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Insert A Special Character Every X Characters In A Text Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I am having difficulty with the following problem:

I have in column A 150 rows of text containing between 4 and 80 words (20 to 300 characters or so) and I need to insert /n every 80 characters without cutting words.

For example:

Cell A1
Hello my name is peter. I am married and have three kids. I work as a postman and enjoy meeting new people.

this should become something like:

Hello my name is peter. I am married and have three kids. I work /n as a postman and enjoy meeting new people.

The use of a simple text-to-column would permit me to reconstruct (using =A1&" "&B1...) the phrase and insert /n every X words but wouldn't take into consideration the number of characters (which is a problem).

If anyone knows a macro or formula that could help, I would much appreciate it.



View Answers     

Similar Excel Tutorials

REPT Function in Excel - Repeat Values
Quickly repeat a value, character, or number within a cell in Excel. This is a neat little function that is easy ...
Change Specific Text within a Cell in Excel
Change or replace text in a cell with other text - you can replace a single character, numbers, letters, etc. This ...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by findin ...
Delete Only the Text from Cells
This macro will delete all text from selected cells. This will not delete any other characters or numbers from any ...

Helpful Excel Macros

Filter Results in Excel to Show Only Those that End With Specified Text or Words - AutoFilter
- This free Excel macro filters data to display results that end with certain words, text, or characters. This is a very
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w

Similar Topics

Could I get some help on this please? I am a Rookie with a capital R !

I have a bunch of rows in column A (2,000 rows or so) that I need to pull 2-4 character words (all text, some words are 2 characters long, some are 3 and some are 4) out of and place in column B. An example would be the term YHJI would be in column A and I would like to COPY it to the same row in column B. It might be mixed up with some numbers or special characters. I need for the macro (or formula) to ignore any word that is 5 or more letters long.

Early thanks to any help you can offer me on this!

Hi All
I hope that my following query will be clear. But if not, please excuse me I am new to Excel (well relatively!) and I'll try to be as straight forward as I can.

I have a column which contains 2500 cells. In each of these cells lies a description of an item. Each cell contains a max of 50 Characters (including spaces etc). 90% of these cells have less than 50, and 10% have exactly 50. I've just been told that instead of 1 cell of 50 characters, I should have 2 cells, each containing a max of 25 characters.

Now my problem is, I've already spent a huge amount of time editing the cells already and I don't want to have to go down through each cell and re-editing them in order to create two cells!

Also I tried the "Text To Column" Function. And while in theory this works, it means that it separates the cell at exactly 25 characters and anything beyond these 25 characters in put into the second cell. My problem with this is that it also chops my words up.

So i want to be able to create two cells, while also preseving entire words. So if that means that one cell must have 20 characters, adn the other 25, then so be it. As each will still contain less than or equal to 25...and most importantly the words are intact.

I hope I'm making sense.

I just think that there must be something that can be done to make Excel do this for me, rather than me having to manually edit 2500 cells of data.

Also if I can Excel to do it for 90% of the cells, i don't mind having to go in and edit 10% myself. I would except there'll be a few cells that it can't be done in, if there are the full 50 characters used, it would be lucky to have them separated at exactly 25 characters, and not breaking up any words.

Any help / suggestions/ prayers are appreciated!!

In my desired code it includes using notepad to create a text(.txt) file that contains one long list of words, where the individual words are separated by the space character.

I need to write a sub that opens this text file, parses the single line with the space character as the delimiter, returns the number of words in a message box, returns the number of words in a message box, returns the number of words with more than five characters in a second message box, and finally closes the file.

Thanks for your help.


In excel is there an alternative to using format|wrap text? I was
wondering if it's possible to insert text from a word document into
excel and have lines automatically cut off after a specified limit of
characters/"whole words" - as it would appear in MS Word. For instance,
if the first paragraph in my word document had 580 characters, I'd like
to paste it into excel. Then I figure you can use a combination of
trim, mid, and other text functions (that I'm unaware of) to return a
specific range of characters.

Let's say cell A1 had 580 character paragraph, and I chose that each
line should have around 100 characters.

I've started using the following technique.

A1 = "paragraph" B1 = 90 (desired character length of each line)
A2 = TRIM(MID(A1, 1,B1))
A3 = TRIM(MID(A1, B1*1+1,B1))
A4 = TRIM(MID(A1, B1*2+1,B1))

But using these formul
as will cut off words
and punctuation where
I don't want it to. Do
you see what I'm tryi
ng to say?

hi all,

I have an excel file that needs importing into the GoldMine application.
(GoldMine needs to have a dbf file for import) As you may know the longest a cell can be in DBF is 255 characters.
the notes in the excel sheet are large (in some cases a few thousand characters)

My idea is to insert a symbol or character at intervals in the text (every 200 characters or so) and then use the "text to columns" function in excel. So i'll end up with about 20 -30 columns of about 200 characters, which should then save ok as a dbf.

The only problem is I don't know how to insert a character (e.g. |) after every 200 characters. I'm sure it must be possible , can anyone help me out with that?


I have some cells in col B where the first characters starting from the left are in bold font. I need to insert four [[[[ characters after the last bold character on the right. By the word "character" I am including numbers, letters, and any punctuation marks. Is there a way to do a macro to insert the four [[[[ characters?

Hey everyone,

I'm really hoping that this isn't a stupid question or worse, one that's been answered and that I just couldn't find using the search function.

The background to my problem is that I'm trying to create a unique dictionary for a game I'm programming. I have imported the dictionary into Excel and need to manipulate it with the following conditions:
Word length can only be 3-5 characters long Word cannot contain special characters like hyphen or apostrophe Word cannot have repeated characters
I got the first two criteria done using pretty brute for methods. The first using the LEN function to find words greater than 2 and those less than 6 and then comparing those two lists to get the list of 3-5 character words. The second I did manual search for characters and replaced them with six random characters so they would fail criteria 1 and be filtered out.

The last one I'm having problems figuring out. The game I'm programming only lets you use a letter once. So words like "dad" (or coincidentally enough "mom") would not be valid.

Is there a way I can look at the words in the cell and see if any character is repeated and then act upon that (e.g., copy to another column if no letters are repeated)?

Thanks in advance!


I have a column with zip codes, some 5 characters, some 9 characters. I need to write an if statment in conditinal formatting that will insert a dash after 5 characters if the length is 9 characters. This is my attempt but does not work:

=if(len(text) > 5, #####, #####-####)

Many thanks, greyhound

Hi Guys,

Great forum!

I have a little problem and I was wondering if any of you geniuses may be able to help me?

Basically I am creating an adwords campaign in excel, however adwords state that the title can noly be 25 characters and the description 35 characters.

A lot of my titles are more than 25 characters, and I can easily truncate the cells to just display 25 chacters, however this cuts off words half way through and looks messy.

I want to be able to display 25 max characters in the first cell, and the rest in the second cell, however only truncate where there is a space.

Does this make sense?


This is a test title and it is purple has 37 characters

I need it to do this:

This is a test title and
it is purple

This seems OK, however it so happened that the 25th character was a space, what about this?

This is an extremely satisfactory product - 41 characters

The 25th chacter is the "i". So i would need it to go back to the space and then truncate to the next cell, like this:

This is an extremely
satisfactory product

Any ideas?

A donation would be gladly made if anyone can help.



I'm using Excel to display what sometimes turns into long descriptions. When these description turn very long (the last time I had this problem it occurred after 1,043 characters) Excel begins cutting off characters. The exact point at which characters are cut off by Excel changes depending on the width of the column the characters are in, though the number of characters displayed does not change to any great degree. The text is of coarse word wrapped. The work around we use is to especially put the returns in for Excel. Thus if it cuts off at 1,043 characters I’ll go back to the first space and use Alt+Enter to put in a new line. This will buy you a few hundered more characters and then you need to put in more line breaks until the entire text displays.

This, as you might imagine is very tedious, and worse if I miss that a cells that is cutting off text it can end up on a power point presentation. Thus my question is this, does anyone know of a way to make Excel stop cutting off the text without manually putting the returns in?

Note: I’m using Excel 11.56… if that helps

I have text cells that contain special characters because people COPY and PASTE text from other word processors. Is there a way to remove SPECIAL Characters from these Cells.

Hi Guys,

Im having some trouble writing a macro that searches through all cells in column E and removes the first 2 characters if the first character is text. I can remove the two characters but I can not specify for it to only do this if the first character is text.

Any help would be appreciated.



I have hundreds of rows with a particular column that has cells with hundreds of characters. I need to trim each cell within this column to 80 characters, add a new row below and paste the reaming characters in the new line.

I do not need to add a line-break, I need to add a new row.

For example, I have a cell with 433 characters. Therefore, I would need to add five new rows for a total of six rows (433/80 characters = 5.48 rows) with the first five rows filled with 80 characters and the last row with 33 characters (80+80+80+80+80+33=433).

Can this be done with VB or a macro and if so can someone please help me with the code?

Thanks so much.


Help with this problem please!

I have a .xls with about 1000 rows of text. The text in each cell is lengthy, running to thousands of words in some cases.

The existing text is in column A and/or column B.

I want to insert some html code {loadposition} into column A if column B is empty, or alternatively into column B if column A and B both have text.

If the html code is going into Column A I want to insert it after 75 words.

If the html code is going into Column B I want to insert it at the beginning of the text.

Any help or ideas gratefully received!



Dear All,

I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.

I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.

If I can determine the position of the last occurance of the special character, I could use the LEFT function.

The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.

Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.

There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.

I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.

Thanks for your help,


I am trying to find a formula that will take into consideration the character length of the cell and the character length of words in the cell.

I would like a formula that will do the following:
If the character length is greater than 20 remove vowels of words that are longer than 6 characters
If the character length is 20 characters or less skip cell.
If A E I O U is the first letter skip.

Original Cell


New Cell


I have included a sample spreadsheet for your convenience.

I need help separating data (60 characters max) that is in one cell into three seperate cells. There can be no more than 20 characters in each cell AND I cannot split whole words.

I have used the MID command to split data (<60 characters) in cell A1 into cell B1 [=MID(A1,1,20)], cell C1 [=MID(A1,21,20)] and cell D1 [=MID(A1,41,20)]. Unfortunately this process splits words exactly at the designated character locations, resulting in single words being split into 2 cells.

Any help?



I need help in solving a problem I have with splitting various length text in a cell. My requirements are to have the column be only 42 characters in length, but I can't truncate the cell because I need whole words, and for the same reason I can't use Text to Column.

The column can contain over 5000 cells with text of various lengths,



I need help in creating a macro that will split the cell string into x number of whole words that do not exceed 42 characters in total length for the cell. If possible I would like to have the remaining whole words inserted into a new row just below the previous split cell.



I have past experience with Visual Basic, but VBA seems to be slightly different. I appreciate any help at all. Thanks

Hello -

I'm trying to split text based on the length of the string (70 char) in a column and insert rows directly below the original cell with the remaining string. It should insert as many rows as needed where the length does not exceed 70 char. I've used vba code that JMHANS posted a while back (thank you!!) and converted it to rows instead of columns ~ the post is called "Split text over 255 characters". My problem is this, I cannot seem to loop through an entire column, it only recognizes the first cell in the range (C) specified and either deletes the remaining cells or does nothing with them. I'm beyond new to VBA and macros and I'm hoping the solution is straight forward!

This is a sample of what I've been working with:

A B C 1 X X characters in cell = 1000 2 Y Y characters in cell = 800 3 Z Z characters in cell = 140

Desired result

A B C 1 X X first 70 characters of 1000 2 X X *insert row 2 of 14 # X X +rows+ 15 X X insert row 14 of 14* 16 Y Y first 70 characters of 800 17 Y Y *insert row 2 of 11 # Y Y +rows+ 28 Y Y insert row 11 of 11 29 Z Z first 70 characters of 140 30 Z Z insert row 2 of 2

As you can see from above, i'm repeating the values in columns A and B which is also desired but that was beyond the first step I was focusing on to just get the insert rows to repeat through the list!

This is the modified code that I'm working with:


Sub SplitCell() 
    Dim Row As String 
    Row = InputBox("Enter the letter of the column that the comments are stored in.") 
    Dim cel As Range 
    For Each cel In Range(CStr(Row & "1" & ":" & Row & "60000")) 
        If Len(cel.Value) > 70 Then 
            newRow = 0 
            Do While Len(cel.Offset(newRow, 0)) > 70 
                temporigval = cel.Offset(newRow, 0).Value 
                cel.Offset(newRow, 0).Value = Left(temporigval, InStrRev(temporigval, " ", 70) - 1) 
                cel.Offset(newRow + 1, 0).Value = Right(temporigval, Len(temporigval) - InStrRev(temporigval, " ", 70)) 
                newRow = newRow + 1 
        End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I did not create this code so if there are questions as to why it was done this way, I don't know! If there is an easier way to do what I'm looking to do, please feel free to post your suggestions! This just happened to be the simplest and most straightforward code I could find!

Thanks in advance!!

I have an excel work sheet with Polish names in displaying as for example Krzyszta³owicz - how can I replace the ³ with an ł character - the character ł is a subset of Latin so I cannot use Chr(108) as this just inerts "l".

I was wondering, if I copy and paste both characters into AutoText (which I can do, I just tried it), how would I loop through the sheet, find words with ³ in them, strip out the characters before and after it and add them to variables, so the ³ is on its own so the autocorrect will autocorrect it and then put the other text back in.

Is there an easier way?

Also, the cell which was changed needs to be a different colour and a report (perhaps on another sheet) should display how many changes were made.

By the way - I have about 20 different characters to change in this manner.
Any help would be advantageous at this stage!!
Thanks - Lynn

I am currently working on doing some translations from English to Spanish. I have been struggling with finding a way other than "insert special character" to make the "n" with the ~ and some accents.

Does anyone knows where can I find the list of shortcut keys to insert those characters? or, even better if you know something that it's better than that.


I have a very large amount of text in column A.

It looks like this:

These words have spaces between them_No_Spaces_More words with spaces___Line 1__
These words have spaces between them_No_Spaces_More words with spaces___Line 2__
These words have spaces between them_No_Spaces_More words with spaces___Line 3__
These words have spaces between them_No_Spaces_More words with spaces___Line 4__

I need to make it look like this:


Replace the space character with the "_"
Remove the trailing "_" characters.
Replace multiple instances of the "_" with just one.

Can you help me do this?

Thank you.


I have several rows of text data, the first 17 characters of which are in this format (4 letters_-_DD.MM.YYYY

It's then followed by a variable number of characters i.e.


I would be grateful for a formula that will delete all characters to the right of the 17th character (i.e. the '3').

Many thanks!

I have a problem that I don't think is unique, but I haven't been able to find a resolution via the usual tools (Google, forums, etc).

I work for a localizer of text heavy software. Our developers normally use a converter to pull text out of a file and insert it into the program. This text is normally input into an Excel file since it has to correspond with files/locations in the program.

Since the space on the screen of the program is limited, we often face character limits when inputting text into Excel. Now, one cell might hold up to 255 characters, and I could restrict that with data validation, but within one cell I need to restrict each line (with a hard return) to 30 characters to fit on the screen.
So for example I have a row of cells that look like:
Hello, my name is Amejin. I like pizza, beer, and bowling.

Which need to look like:
Hello, my name is Amejin. I[HR]
like pizza, beer, and bowling.

Does anyone know a simple way to do this, or do I need to look for a macro and a VB expert?

I have a column of data (Text Only) (A1:A350) with varying data lengths (min 12 characters max 44 characters). I would like to format column A1:A350 as such the character length of the cell matches the longest cell character length. Meaning, if Cell A1 is 12 characters and Cell A200 is 44 characters, I would like to add the balance (24) characters to Cell A1 to equal 44 characters.