|
How to count number of characters in xl cell for publisher merge
I am doing a catalog merge in publisher. Data is in XL. Publisher has 255
character limit on merged info. How do I count the characters before I execute the merge? |
How to count number of characters in xl cell for publisher merge
Use the LEN (Length) function:
=LEN(A1) -- Biff Microsoft Excel MVP "msloell" wrote in message ... I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? |
function - LEN
Hi Biff!
using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP EggHeadCafe - Software Developer Portal of Choice ADO.NET 3.5 Cookbook by Bill Hamilton [O'Reilly] http://www.eggheadcafe.com/tutorials...book-by-b.aspx |
function - LEN
Your cell isn't empty.
Try selecting that cell and hitting the delete key on the keyboard. What happens to that =len() formula? If it stays 1, then maybe you have calculation set to manual. Tools|Options|calculation tab is where you'd change it in xl2003 menus. My guess is that you have some white space character (space bar or HTML non-breaking space????) in that cell. matt, hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP EggHeadCafe - Software Developer Portal of Choice ADO.NET 3.5 Cookbook by Bill Hamilton [O'Reilly] http://www.eggheadcafe.com/tutorials...book-by-b.aspx -- Dave Peterson |
function - LEN
Wrong, Matt! LEN does not return 1 for an empty or blank cell.
For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; you have a character in there. You can see which character with =CODE(A1). If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1) |
function - LEN
Wrong, Matt! LEN does not return 1 for an empty or blank cell.
For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; you have a character in there. You can see which character with =CODE(A1). If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1) |
function - LEN
How did you know his name was Matt, David?
Pete On Oct 21, 11:32*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! *LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; *you have a character in there. You can see which character with =CODE(A1). *If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text - |
function - LEN
using the =len(a1) works fine unless the cell is empty/blank;
.. it returns count of 1 for blank cell The result should be zero, not 1 You probably have an invisible white space which is causing the error Try usingTRIM: =LEN(TRIM(A1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
function - LEN
Well, Pete, I was working from the part that said: "matt hinkle wrote:"
-- David Biddulph Pete_UK wrote: How did you know his name was Matt, David? Pete On Oct 21, 11:32 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; you have a character in there. You can see which character with =CODE(A1). If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text - |
function - LEN
"Pete_UK" wrote:
How did you know his name was Matt, David? In Google Groups, click on Hide Options to see the From header record. |
function - LEN
"Visitor" wrote:
"Pete_UK" wrote: How did you know his name was Matt, David? In Google Groups, click on Hide Options to see the From header record. I meant More Options. It changes to Hide Options after you click on More Options. |
function - LEN
Maybe from the post that matt hinkle authored?
matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? Gord On Wed, 21 Oct 2009 15:56:17 -0700 (PDT), Pete_UK wrote: How did you know his name was Matt, David? Pete On Oct 21, 11:32*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! *LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; *you have a character in there. You can see which character with =CODE(A1). *If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text - |
function - LEN
Of course !!
All I saw was "- Show quoted text -" after your post, and I didn't think to expand it. Thanks to all for setting me right <sheepish grin Pete On Oct 22, 12:24*am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Well, Pete, I was working from the part that said: "matt hinkle wrote:" -- David Biddulph Pete_UK wrote: How did you know his name was Matt, David? Pete On Oct 21, 11:32 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; you have a character in there. You can see which character with =CODE(A1). If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
function - LEN
On Oct 21, 7:00*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Maybe from the post that matt hinkle authored? matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? Gord On Wed, 21 Oct 2009 15:56:17 -0700 (PDT), Pete_UK wrote:How did you know his name was Matt, David? Pete On Oct 21, 11:32*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! *LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; *you have a character in there. You can see which character with =CODE(A1). *If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text - |
function - LEN
Thank you so much for clearing that up! There ARE spaces involved; I
didn't even think to check for that! Thanks again for the expert help! On Oct 21, 7:00*pm, Gord Dibben <gorddibbATshawDOTca wrote: Maybe from the post that matt hinkle authored? matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? Gord On Wed, 21 Oct 2009 15:56:17 -0700 (PDT), Pete_UK wrote:How did you know his name was Matt, David? Pete On Oct 21, 11:32*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: Wrong, Matt! *LEN does not return 1 for an empty or blank cell. For either a completely empty cell or a cell with the empty string ="", LEN returns zero. If you are seeing a LEN of 1, you do not have a blank or empty cell; *you have a character in there. You can see which character with =CODE(A1). *If it returns 32, you have a space, if it returns 160, you have a non-breaking space. -- David Biddulph matt hinkle wrote: Hi Biff! using the =len(a1) works fine unless the cell is empty/blank; it returns count of 1 for blank cell--how do I account for these? I assume it would be an if statement of some kind?? T. Valko wrote: How to count number of characters in xl cell for publisher merge 31-Aug-08 Use the LEN (Length) function: =LEN(A1) -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Sunday, August 31, 2008 4:19 PM msloel wrote: How to count number of characters in xl cell for publisher merge I am doing a catalog merge in publisher. Data is in XL. Publisher has 255 character limit on merged info. How do I count the characters before I execute the merge? On Sunday, August 31, 2008 4:26 PM T. Valko wrote: How to count number of characters in xl cell for publisher merge Use the LEN (Length) function: =LEN(A1)- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 07:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com