Remove all characters before or after X# of characters

**Ocean Zhang** · 07-10-2014, 07:52 PM

I need to enter data in 2 columns that have a character limit of 30 each. My data has more than 30 characters. I'm looking for a formula that can trim my data and keep everything <= 30 characters in column A, then a second formula to put everything that's >= 31 into column B.

Thanks!

**Pete_UK** · 07-10-2014, 07:59 PM

Assuming you already have data in column A, put this formula in B1 to get the first 30 characters:

=LEFT(A1,30)

and this one in C1 to get the remaining characters:

=MID(A1,31,255)

Copy both formulae down, then fix the values and you can delete the original column A.

Hope this helps.

Pete

**FDibbins** · 07-10-2014, 08:06 PM

Another way would be to use Text2Columns with Fixed Width, move the slider to 30

**Ocean Zhang** · 07-14-2014, 01:35 PM

Thanks Pete and Ford! I need to add another wrinkle to this. I realized I needed whole words to be intact. Cutting cells off at exactly 30 characters every time will occasionally bisect a word. How can the formula be modified to get column B to cut off after the last space before reaching 30 characters. Then column C to pick up where column B left off?

Thanks!

**hoyasaxa215** · 07-14-2014, 01:59 PM

Does your data have spaces between each word? Can you upload an example file?

**mikerickson** · 07-14-2014, 02:48 PM

Regarding whole words. Does your data have punctuation, "Bob, the district manger, sent an email"
What do you want done if the last character is punctuation. Eliminate it (so it appears in neither column) or treat it as part of the preceding word?

**sktneer** · 07-14-2014, 11:09 PM

If you are open to a VBA solution, please find the attached sheet to see if this works as per your requirement. Click the button on sheet1 to split text in col. A as per your requirement.