Insert A Special Character Every X Characters In A Text Cell


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.



Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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 ...
Formula to Delete the First or Last Word from a Cell in Excel
Excel formula to delete the first or last word from a cell. You can copy and paste the formulas below for a quick f ...
Wildcards in Excel
Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...

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

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!


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 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.

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.


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 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'm trying to clean up a variable length column in Excel in order to import into Monarch so I can properly trap the data.

If I have variable data in column A with # characters ranging from 1 through 40 characters, what code would create a new column where every cell had 50 characters (using spaces as filler)?

For example in cells A1 and A2 is the text:

Mark (4 characters)
Mark's Excel Problem (20 characters)

I want spaces inserted for total character count of 50 characters (the period represents a space for visual representation on this post):
Mark..............................................(4 characters plus 46 spaces = 50)
Mark's Excel Problem.........................(20 characters plus 30 spaces = 50)


I've just joined but I frequently find many answers to my queries by searching on the forum, excellent resouce.
I am working on a project in VBA for Office 2003. I've got a userform set up with text boxes and some of these text box fields need to be fed into an e mail. I'm fine with all the e mail side of it but where I am stuck is that each line of the e mail can only be 40 characters long as a maximum. So I figured I can use the Len function easy enough and split the text string from the box down into 40 character segments...But then I realised, I am working with titles of papers and books and such like and what I really need to do is split the text into a maximum of 40 characters providing the split doesn't end in the middle of the word, in which case I need to split it at the space between the words..
Can anyone help me out please? Here is an example of what I mean:
I have the following text entered into the text box:
"Proceedings of 4th International Porifera Congress: Sponges in time and space"
But as it is over 40 characters long if I count to the 40th then that leaves me with:
"Proceedings of 4th International Porifer"
But what I really need is to have the text split as:
"Proceedings of 4th International"
And then the next 40 characters counting on from the end of "Interrnational" etc and keep going until I have got the complete title split out into complete word segments of 40 characters or less.
I don't know if that makes sense, would be very greatful for any advice.

I have a txt cell with between 1 and 200 characters in lenght. I'm trying to
break it up into 1 to 6 seperate cells, each with up to 40 characters in it
without splitting up any words. So if the 40th character splits a word, I
want to split it at the previous space. Then I want to go from that character
forward for the next cell, and so on.



I have a column of cells each containing a text string of varying lengths.

What I want to do concatenate the text together (found a nice UDF that does this by specifying a range, rather than use the standard concatenate forumla) and then I want to remove common words such as "the", "as", "it", etc. to try and ensure the volume of characters stay beneath the character limit for a single cell for Excel 2007, which I believe is slightly over 32000.

I could (and have so far), got a list of the top 500 words in the English language and used SUBSTITUTE to replace the common words. But this produces either a huge single cell forumla (with one SUBSTITUTE per word - but I eventually hit a limit on the length of the formula), OR, I do a SUBSTITUTE for each word I want to replace in it's own cell, creating a huge column or row of cells with a SUBSTITUTE formula.

As I am dealing with over 13000 cells with text in them, the worksheet quickly become very unhelpful in its size.

Is there any UDF (called, say, REMOVE_COMMON()) that could reference a cell of clutered text and then replace the common words by either looking up the words to be replaced from a separate column of words elsewhere in the workbook, or have the words to be replaced listed within the UDF itself, that one could then go in to the VBA (using Alt + F11) and add the extra words that need to be removed?

My end goal is to create a single cell of text that I can copy off and paste the raw text into I know Wordle ignores common text, by the problem is, there is SO much text in the 13000 rows of text data, that I need to find a way to reduce the volume of words BEFORE pasting into Wordle, as Wordle itself seems to have a limit to the number of words you can paste into it.

Hope this is clear. Any help would be much appreciated and save me a whole load of huge worksheets.

Kind regards


Is it possible to write a macro in excel sheet to convert characters to corresponding hex code value.

When the macro is run, all the special characters should get converted to hex code.

By special characters I mean all the characters which are within this range

Latin-1 Supplement (0080-00FF)
Latin Extended-A & B (0100-024F)
General Punctuation (2000-206F)
Currency Symbols (20A0-20CF)
Mathematical Operators (2200-22FF)
Combining Diacritical Marks (0300-036F)

In other words, if a character is encountered in the excel sheet, that has a hex code which falls in any of the above ranges, then it is considered to be a special character and converted to hex code (in the excel sheet).


Is there a simple excel formula which will extract a text string from another text string.

The text string to be extracted will usually start in the middle of a text string (but may be the first or last part of the string).

The text string always begins with at least the same 3+ characters.

If / when this can be done, it would be useful to also control the number of words being extracted starting from the start of the text string being extracted, that is rather than use a wild card (*) to take all characters from the end of the text string.

Kind regards

Good afternoon,
I am building a macro to automate a quarterly process. The powers that be want the report generated by this macro to look exactly like the manually created report. The manual report uses special characters to illustrate progress, a red triangle pointing up or a black triangle pointing down. They copy and paste these characters from the character map.

I know how to determine which character should be used, but I cannot figure out how to insert the special character. Any ideas?


For Each c In Range("C4:C28")
    If c < c.Offset(, 8) Then
    c.Offset(, 1).Value = "?????????"
    End If
Next c

I am using Excel for some textual and numeric analysis. However, I am finding a limitation in its use for looking up words in a Hebrew morphology table - approximately 40,000 word forms found in the Hebrew Bible. The words exhibit as Hebrew words using a font named "BWHEBB" which "translates" from a Roman character (English) keyboard. The problem centers on the use of certain diacritical characters to represent Hebrew letters used as finals and the letter 'ayin'. So the words stored in the table may contain [, ~,!, @, #, $ as well as Roman letters. Although I have sorted the table A-Z wise, certain word lookups cannot be executed, either with LOOKUP, VLOOKUP or even with the FIND tool (under Home-Editing-Find&Select).

Is this perhaps a deficiency in Excel? Has anyone a thought to overcome the difficulty?

Hi All:

I seek the knowledge of the experts once again...

Can you tell me if it is possible to have the active cell change after a certain number of characters are entered?

I have a spreadhseet that people enter a 36 digit code into. The different fields have a different amount of characters in them. The string is as follows:

J17 can enter 3 characters
K17 can enter 6 characters
L17 can enter 4 characters
M17 can enter 6 characters
N17 can enter 6 characters
O17 can enter 4 characters
P17 can enter 4 characters
Q17 can enter 4 characters

SO what I would like to happen is the user enters the 3 digits in J17 and then the active cell becomes K17 where they enter 6 digits and then the active cell becomes L17... Does this make sense? Currently the user has to Tab to the next cell but if they could enter the number contu=inous that would be great.

Any ideas?


If I have cells with various words in varied length, is there a way to format the cell to display only the first 3 characters, the first 5 characters, etc... while the actual cell contents are say 20 characters?

I have never had a request like this before so please excuse me if it is an obvious answer...

Thank you

I have a simple question: I have a single column with text values.
Each cell has 4 non useful characters: 3 numbers and a comma (,) - see file attached
Is there any way to remove this 4 characters in each cell, using a formula or a macro?
Thank you!

I have a problem.

In order to compare strings I have to remove a special character from imported text.

It is character F008 from Unicode(hex).

This charcter is not on my keyboard.

I find it under Insert - Symbol but I can't copy and paste it to the find/replace window.

Any idea how can I key in this character so that I can use it for find and replace?


I have a field (column A) of text data. The value in the cells can be between 2 and 5 characters in length.

In column D, I must maintain the value in column A, however, the text string in column D must be exactly 15 characters long. The extra characters must be spaces.

eg. A1 = "CW", therefor D1 must equal "CW-------------", where "-" are spaces.
A2 = "BOOPY", therefore D2 must equal "BOOPY----------"

Sometimes there is no value at all in column A. In that case, column D equals the value in column B which is a constant 14 characters long. Again, column D must be 15 characters long.

eg A3="", B3="n000.00.00.000", therefore D3 must equal "n000.00.00.000-", where - is a space

So far I have this formula in column D, which so far only determines if A is empty, then substitutes a concatenated value of B + one space when A is empty.


What it doesn't do, and for which I come herer for help, is to assign D a 15 character value based on the value in A.

Thank you in advance to anyone reading and able to help me with this.

I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER).

How do I search for this special character to replace it? How do I identify what that invisible character is?


I have a field that is not a fixed length. It contains characters before and after a colon (":") (including spaces). I need to eliminate all of the characters after the ":" and just return the characters and spaces before the ":". The number of characters before the ":" varies (or I would just use Left()). Would someone please help me with a formula to return only the characters before the ":"?
Thank you.

Hi guys,

I have some text which is centred across selection and the text wrapped so that it resizes nicely and this has been fine. However, today, I have come across a problem. I autofit the height of the cells at the end and I am finding that the row is autosizing to double character height even though there is in fact only a single row of characters. If I add or remove a few characters, normal behaviour is resumed - few less characters and the row resizes to singly, few more and it is double but some characters have now moved to the second 'character' line.

The question is... can I rectify this? I would rather not have to tell the end user that he simply has to keep his eyes open and change his sentence a bit if it happens!

Many thanks

Edit to add: I have been playing about with this and it even happens when I put the text into a single cell of the same width.... it seems although, when the text is close to the width of the containing area that excel gets the autoheight wrong.

Hi All,

Helping a friend at work with a macro, but am finding that there are some unprintable, unseen characters that appear after we load data into the spreadsheet that we can't seem to scrub.

When I paste the string into MS Word, it looks like the attached. I'm not sure what those characters are, but I need to replace each one with a space (the number of spaces before the text in each cell is an important part of the macro.)

So the question is, what are those characters, and how can I replace them with spaces?

Thanks, as always, for any assistance.

I reinstalled my Windows XP and all my computer applications, including
Office 2000 (Japanese version) yesterday. I've also installed the East Asian
files and Japanese input IME.

Everything works fine except that now in Excel 2000, I cannot input Japanese
characters directly into the cell. Everytime I try to type Japanese
characters, a small dialogue box pops up and my Japanese characters appears
there. Only after I press the enter key will the characters appear into the
cell that I selected.

Previously, typing Japanese words in the cells will be the same as if I were
typing English characters. The problem is so irritating. Does anyone know how
I can revert back to the previous way? Is there any setting that I should

I've been pecking around for a while and have had no luck.

I have a column which is filled with text. The cells have between two and five characters. All cells end in either "A" or "R". I need to replace all of the last characters - all A's become N's, and all R's become A's. Problem being, A and R appear in the text in places other than the last character.


Does anyone have a quick macro solution for this? Thank you very much!