Close Window
Free Ebook
Get Your Free Excel
Top 15 Excel Tutorials from
Instant Access!

Split Cell Depending On Characters And Spaces

Hi, i have groups of data and If the string is longer than 40 characters, I need the text before and after split into seperate columns but at the last space before the 40 character limit.

For Example:

String:This is the long string that i want splitting into two columns

Result1:This is the long string that i want
Result2:splitting into two columns

If i split at 40 characters i would split a word so want it doing before this occurs.

Thanks for any help.

Free Excel Courses

Similar Excel Tutorials

Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...

How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...

How to prevent a user from entering any spaces within text in Excel.  This allows you to keep data clean when a use ...

View different sections of the same worksheet when the sections are far apart.  This allows you to better work on ...

Helpful Excel Macros

- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f

- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the

- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl

- This free Excel macro filters data to display results that end with certain words, text, or characters. This is a very

- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell

Similar Topics

Hello All

I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)


What I need to do is split each of the characters in to its own cell

if JN551122B was in cell A1 I'd want to return:

B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B

The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

Thanks for your help!

Hello All, I have a column where cells need to be 50 characters long, it contains addresses, however, since all the addresses do not meat the criteria, I need to add (blank spaces) to reach the 50 characters, any idea on how to approach this?


Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell

Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.

Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.

Any help anyone could provide would be WONDERFUL. Thanks!


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 merged cell which contains a text with multiple lines.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.


I am looking for a formula to remove special characters and spaces from a cell

I have been using "substitute" but this requires me to know which character I want to remove and this isn't always known

I have tried looking at some macro solutions but became lost quite quickly

Any help would be much appreciated


i have a cell i have to check if it contains six characters. I have a list of data that i need to narrow down to six characters. I have successfully done that, but some of the cell has 5, 6, or 7 characters. The list contains about 600 cells, but i don't have time to format them individually. I want to create a formula that returns true or false if the cell contains 6 characters and false if it is above or below 6.

Can somebody help me, i'm new to excel!

I need to find data in two different formats within a column


Webb Christopher

Greer Nancy

I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.

I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.

Thank you for your help!

Hi all,

Probably a simple little function, help would be appreciated!

I'd like to take a number entered into one cell ie. A1 = 20

and split it randomly but evenly over 3 other cells ie.

Number should be whole and no zeros?

Thanks all!

I have a large worksheet. Over 20,000 rows. I want to split every 900 rows into new worksheets. I need each worksheet to be 900 rows or less when done. Any macro would be helpful.

Hey everyone - how would I create an IF statement that looks at the first two text characters of a particular cell?

I am looking for a formula to remove special characters (like ', " & - ) in a sheet.
Thank you.

I am looking for a method/formula that will reverse multiple text entries from "abcde" to "edcba". The entries are composed of several words that need to be reversed ie from "ab cde fg" to "gf edc ba"


I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines.

How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..

I would like to create a formula that removes a specific character if it appears in a cell. In this case, if the text in the name cell starts with * or #, remove it. Otherwise, keep the contents intact. Examples:


Text in Cell Desired Results
*Bobby Abreu Bobby Abreu
#Erick Aybar Erick Aybar
Jason Bartlett Jason Bartlett

Is there a formula that will get me where I want to be?



Hi there, I have a string of numbers and I would like to add zeros to the end of it.

For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).

I would do it manually but I have a column of over 2000 different numbers.

Thanks for your help, you guys are great!

Hi all,

I'm trying to extract all the text in a cell which is on the right side of a comma (,)

Currently I am using this formula,


However it sometimes is missing some of the data after the, and sometimes pulling a few characters before the,

any suggestions on how to correct my formula please?

Thanks in advance.

I'm trying to freeze the widths of columns so that when users of the
worksheet are using it, they can't change the width. It's for editors who
write headlines for specific areas that can only be as long as the column is
wide or the text won't fit.


As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).


Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
For Each C In Range("A1", Range("A65536").End(xlUp))
 If C 0 Then
 With C.Offset(0, 1)
 Set Picture = Nothing
 On Error Resume Next
 Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
 Picture.Top = .Top
 Picture.Left = .Left
 Picture.LockAspectRatio = msoTrue
 Picture.Width = 157
 Picture.Height = 138

 End With
 End If
Next C
End Sub

I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help


This may be less of an Excel question than a general data display question, but I'll try here.

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.



How do I restrict what I see on my screen (and specifically, limit the scroll
feature) to the rows and columns I'm actually using in an Excel 2003 Pro

I need to create a bar chart for the year. I've already have 3 bars for each of the months, a plan, an actual, and a last year. I need to be able to split out just the actual bar, so that it is a stacked bar. The other two bars need to stay and remain unstacked. Is this possible?


I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.



I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.

Sorry to ask such a bizarre question, but I appreciate your help.


Related pages

calculate upper control limit in excelhow to calculate man days in excelconvert stone to kilogramswhat is row height in excelexcel vba application.gotovlookup for range of valueshow do you auto fill dates in excelvba outlook calendarprorated raisemultiple sumif criteriaexport excel to notepadhistogram vbaexcel to kml converter freespeedometer chart in excelhow do you remove a hyperlink in excelsubtraction formula on excelmerge excel files into one worksheetexcel opening blankword vba message boxcheck spelling excelhow to find mean median and mode in excelcircular references in excelconvert kgs into stonesyield maintenance penaltywhat does the exclamation mark mean in statisticsclass does not support automation or expected interfaceexcel macro select rangepricing matrix excelpayslip in excelbank reconciliation excel spreadsheetvba freefilefinding slope in excelexcel 97-2003 workbooktranspose columns to rows excelhow to calculate annual leave entitlementexcel 2010 combine two columnshow to change the width of a cell in excelweek ending date in excelexcel 2007 concatenateucase vbahow to unlock cursor in excelnot equal vbafuta calculationfilter unique records excelexcel quartileexcel vba elseifvba constant expression requiredvba on error go toexcel vba run macroboolean excelvacation calendar excelmerge multiple sheets into oneexcel macro rename fileexcel combine pivot tablesexcel merge worksheets into oneobjexcel.workbooks.openruntime error 430 class does not support automationwhat is denormalized datainstall solver in excelconvert gradient to degreespivot table vba codeexcel vba sql connectionhow to recover files from excellinear interpolation excel 2010subdividersmean kinetic temperature calculatorgolf handicap calculator spreadsheetsharpe ratio formula excelleast squares regression method excelcombine graphs excelexcel macro option buttonvba password codediff excel filesexcel unprotect macrovba xldownexcel named range changeraffle name pickerexcel copy merged cellsirr excel calculationselfcert.exe