Measuring the amount of single and double byte characters in a cell

[Translationguy]

Hi All

I have (what is probably) quite an unusual problem where I frequently need to measure the amount of single and double byte characters in an Excel cell.

The reason for this is that Google allows a fixed number of characters for PPC such as ads but for double byte languages, it counts any single byte characters such as spaces and English text as 0.5. Therefore whilst the character limit they have might be 12, I could use 9 double byte characters and 6 single byte characters and still make it fit.

If it is over this limit, the piece will be rejected and can;t be used whatsoever.

The current method I have is to count the number of single byte characters in the text, put the number into the cell next to it and put in a formula such as "=LEN(a1)-(a3/2)".

"A1" is the text I am measuring, "A3" is where I put the manually counted number of double byte characters and the result will tell me how Google would view it. I tend to put in conditional formatting to highlight the ones that are too long however this is quite a manual process.

I understand that Excel has the ability to count bytes with "=lenb" however it hasn't worked when trying to measure 2 different byte lengths in the same cell.

The cells will never follow the same pattern however changing the cells the formula is referencing isn't too much of an issue for us.

If anybody knows of a solution I'd be really grateful as it will save a lot of time for myself and my colleagues.

With many thanks!

 

[Translationguy]

.

 

[Translationguy]

Hi All

I've just shared this with a colleague from another department however they have 2010 which will show "#NAME?" when using the formula

PGC01 mentioned using UDF however I'm not sure how to use this or whether it's capable to be used a single cell.

The ideal scenario would be that we can have the same functions of this above formula but that it works (in a single cell) the same way across different versions of Excel so that neither our department nor others could be compatible.

Any help is appreciated!

Many thankshttps://www.mrexcel.com/forum/members/pgc01.html

 

[pgc01]

Hi

UNICODE() is a function that is only available in excel 2013+

If you need to work with earlier versions of excel you'll have to write a udf.
In vba you had already since long ago the function AscW(). You can use it to get the 16bit code of the character.

This is a simple example with the same logic as the formula.

Use: =nBytes(A1)

with the udf:

Function nBytes(s As String) As Long
Dim j As Long, k As Long

For j = 1 To Len(s)
    ' add 1 byte for each character
    k = k + 1
    ' if the character code is more than 255 add 1 more byte
    If AscW(Mid(s, j, 1)) > 255 Then k = k + 1
Next j
nBytes = k
End Function