| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge a cell based on count of characters | Excel Discussion (Misc queries) | |||
| How can I count the number of characters on a cell? | Excel Discussion (Misc queries) | |||
| Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
| How do you count number of characters in a single cell | Excel Worksheet Functions | |||
| How do you count number of characters in a single cell | Excel Worksheet Functions | |||
Linear Mode
