Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Insert A Special Character Every X Characters In A Text Cell

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

Hello,

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.

Thanks,

Mike

View Answers     

Similar Excel Video Tutorials

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.

Hello,

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

cheers
ICW


Hi, I am sure that the excel guru's in here will cringe when I say what I have done, but I have a worksheet which performs many caluculations for me. Alsongide the 'working' bit of the sheet, I have merged a range of cells (E6:F32) so that I can enter a load of descriptive text. I know that excel isn't designed for this kind of stuff, but I can't do the other calulations in word, and I want it all in one document.

My problem is that (despite having word wrap on), at a certain point the text stops moving onto a new line and just continues right across the screen. I have been trying to fathom if it happens after a certain number of characters, or words - but there doesn't seem to be any consistency - for example:

On one worksheet the merged cell contains 222 words / 1427 characters (inc spaces), but it stops moving onto the next line after 173 words / 1096 characters (inc spaces).

On another sheet the merged cell contains 151 words / 1361 characters (inc spaces), but it stops moving onto the next line after 131 words / 1118 characters (inc spaces).

On one sheet it stops after 21 lines, on the other 22 lines.

Can anybody offer any insight into why this might be - and if there is a solution?

Many thanks

Rich


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!

~Paik


I am inserting a large amount of text into a worksheet and I am wondering what the best way to do this is. I want the text to "wrap" like it would in microsoft word, but I am not sure what the best method is to accomplish this.

I am guessing I would need to count characters, to match my desired width, then insert those characters in the cell, grab the next set of characters, and insert those in the next cell and so on until the insert is complete.

I am hoping there is a cleaner way.

Thanks for the help 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?

Example:

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.

Cheers,

Mike


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 many text strings (sentences of text) in the body of an Excel file. In some of those strings there are bolded words. I want to search through those strings for the bolded characters so I can extract those words and go on from there.

I know how to set up the search across the rows and columns and how to step through the strings for the bolded characters. (I'd just "brute force" my way by counting through the strings one character at a time). I just don't know what the search criteria would look like.

Thanks in advance for your 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.


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!

TIA

Paul


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.

JK

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.

Regards

Phil


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,

GL


Hello,

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,

Example:

Cell A1 = "BLOOMINGTON, IL, CENTRAL IL REGL APT AT BLOOMINGTON NORMAL APT"
Cell A2 = "DICKINSON, ND, DICKINSON - THEODORE ROOSEVELT RGNL APT
Cell A3 = "DOUGLAS, WY, CONVERSE CNTY APT"


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.

Example:

Cell A1 = "BLOOMINGTON, IL, CENTRAL IL REGL APT AT"
Cell A2 = "BLOOMINGTON NORMAL APT"
Cell A3 = "DICKINSON, ND, DICKINSON - THEODORE
Cell A4 = "ROOSEVELT RGNL APT"
Cell A5 = "DOUGLAS, WY, CONVERSE CNTY APT"

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

I have large text files that I want to add various characters every X number of words to separate them. Can excel/macros/anything else perform a function like this? Inserting whatever character/s or string of text that I want every X (defined by me) number of characters/words in the whole file?

Thank you.

I was able to get rid of the weird character [] with the =CLEAN(E11), where E11 had the problem character. but now where the [] used to be between to words the 2 words just show as 1. Example If the bad cell was just[]test the clean cell has justtest without a space between the 2 words. Since the spreadsheet is about 8,000 cells and has random [] characters is it possible to not just clean the bad characters, but add a space where they used to be all in one command?

Any help is greatly appreciated as I have been working on this 1 export for weeks


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:

VB:

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 
            Loop 
        End If 
    Next 
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


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

Thanks!


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:

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


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