Excel text hacks?
June 2, 2006 1:50 PM Subscribe
Stripping out extra text in Excel?
I'm got some data in an Excel workbook that will be going to a fixed-width table on a web page. For purposes of keeping everything nicely squared-up, I'd like to limit the amount of text in column A to 21 characters. Is there a way to automatically strip out any text beyond that character mark? Validation just tells me which cells are over limit, but doesn't actually strip out the excess. Googling around hasn't turned up anything useful. How 'bout it mefites?
I'm got some data in an Excel workbook that will be going to a fixed-width table on a web page. For purposes of keeping everything nicely squared-up, I'd like to limit the amount of text in column A to 21 characters. Is there a way to automatically strip out any text beyond that character mark? Validation just tells me which cells are over limit, but doesn't actually strip out the excess. Googling around hasn't turned up anything useful. How 'bout it mefites?
Another way to do what you want is to go to a blank column (Let's say the data you want to truncate is in column A) put the formula =left(A1,x) where "x" is the number of characters ou want. So, if you have abcdefg in cell A1 and then you put =left(A1,4) in another cell, it will say, abcd. Does this help?
posted by apark at 2:00 PM on June 2, 2006
posted by apark at 2:00 PM on June 2, 2006
You could also enter the next in another column and the refer to it using =LEFT(A1, 21).
posted by mullacc at 2:01 PM on June 2, 2006
posted by mullacc at 2:01 PM on June 2, 2006
You could do it with the Left function.
"Left(A1,21)" will give you the first 21 characters of cell A1.
But again, it's going to need another column. Or maybe if you want to keep it clean, a whole other worksheet? The second one can just have the content of the first one, except truncated using that function. Then you can work in one and export the other.
posted by AmbroseChapel at 2:08 PM on June 2, 2006
"Left(A1,21)" will give you the first 21 characters of cell A1.
But again, it's going to need another column. Or maybe if you want to keep it clean, a whole other worksheet? The second one can just have the content of the first one, except truncated using that function. Then you can work in one and export the other.
posted by AmbroseChapel at 2:08 PM on June 2, 2006
Response by poster: Just amazing, you guys. I went w/ willnot's quick and dirty method, but I'm to try all of these and compare. Thanks!
posted by Gilbert at 2:09 PM on June 2, 2006
posted by Gilbert at 2:09 PM on June 2, 2006
I think MID is a good function to learn. It takes 3 inputs.
1) which cell contains the data
2) which character position should you begin at
3) how many characters would you like to return
Example:
=MID(B7,1,21)
This will return 21 characters from cell B7, starting from the first character.
This would work for this need, but MID can also do fun things like remove the first 3 characters from every cell in a column. And combined with FIND, you can easily return all text found AFTER a certain string, no matter what position that string is in. Cool shit.
posted by scarabic at 2:20 PM on June 2, 2006 [1 favorite]
1) which cell contains the data
2) which character position should you begin at
3) how many characters would you like to return
Example:
=MID(B7,1,21)
This will return 21 characters from cell B7, starting from the first character.
This would work for this need, but MID can also do fun things like remove the first 3 characters from every cell in a column. And combined with FIND, you can easily return all text found AFTER a certain string, no matter what position that string is in. Cool shit.
posted by scarabic at 2:20 PM on June 2, 2006 [1 favorite]
Can't tell if this is needed, but if you no longer need the excess characters, and you've got this new column that has formulas in it which are dependent on the original column of numbers being there, and you want to ditch the original, you can copy the new column of numbers, go to a new blank column and do Paste Special > Values. Now it's no longer formulas, just the results of the formulas, and not tied to anything. All other data can be deleted.
posted by kookoobirdz at 3:21 PM on June 2, 2006
posted by kookoobirdz at 3:21 PM on June 2, 2006
This thread is closed to new comments.
Data > Text to Columns...
Select the fixed width choice. hit next and you'll be able to select the width.
Once you're done, you can delete the column with the extra data.
posted by willnot at 1:55 PM on June 2, 2006