ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

7 Q & A posts tagged with LEN

Sun
May 2
2010

I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?


Answer

Did you try the formula LEN
LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this
=LEN(C1)
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

Sunday 5/2/2010 6:53:36 AM
Mon
Apr 12
2010

How do I find an 10 character alphanumeric string in a Excel spreadsheet cell with 70+ characters?

I need to find a 10 digit character string that is in a cell in Column G that has 70 plus characters. It can be located anywhere. The 10 character string will have these unique attributes:

* 10 characters long
* 1st two characters will be alpha (can upper or lower case)
* last 8 characters will be numeric

The following formula provided by this forum gives me a TRUE return if the this string is the only data in the cell, starting in position 1.

=AND(LEN(F2)=10, CODE(LEFT(F2,1))>64, CODE(MID(F2,2,1))>64, ISNUMBER(VALUE(RIGHT(F2,8))))

How can this formula be modified to:

1.) Allow me to find the string anywhere in the text
2.) Place the 10 digit string into Column H

Thank you.
Your assistance is appreciated.

Mark


Answer

I would do it if I only have the file

mail it to me so that I can do what you want

Trust me, If Excel can do it, then VBAXLMan can do it

Monday 4/12/2010 5:10:35 AM
Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM
Tue
Apr 7
2009

Auto Generate Calendar in Excel?

Anyone know how to generate a calendar, in excel, with appointment entries from worksheet data as a sourc?. Just like creating a graph and using worksheet as data source. Want to do the same thing, but populate a calendar. Thanks


Answer

Here is a free calendar for any month any year
It also has the ability to show your appointments for a full year

http://www.file1.net/project.asp?id=k7fr…

Enjoy it

VBAXLMan

Tuesday 4/7/2009 5:43:51 AM
Fri
Jan 16
2009

Excel - How to get the frequency of the words in a para?

The para contains 1000 words. I need to find out the the top 50 words appearing most often in the para together with the number of appearances only using excel commands - can't use VBA or any other s/w!

any help ??


Answer

The only way to do it is to convert that para into cells (For temporary)

Now, if you have that cell in A1, paste this in B1
=LEFT( A1,SEARCH( " ",A1)-1)
And this in C1
=LEN( B1)
And this in D1
=COUNTIF( B:B,B1)
And this in B2
=MID( $A$1,SUM( $C$1:C1)+2,SEARCH( " ",$A$1,SUM( C1:C1)))
and copy C1 to C2, D1 to D2

Now copy cells B2, C2 and D2 down until you find all the text you have in that para in the cells of column B

Now, the D column will give you the number of any corresponding text found in cell A1

The interesting thing, is that when you change cell A1, all of these will be changed automatically


Enjoy it, Yes, I know I am good at this, I am the VBAXLMan here

Friday 1/16/2009 6:13:55 PM
Fri
Jan 16
2009

Is it possible to add a calendar alert to Excel spreadsheet?

I've created a Excel '07 spreadsheet as a calendar. Is it possible to insert pop-up alerts/reminders in the spreadsheet?


Answer

Yes, using macros, for example:

Put this code in the Workbook module in the VBA (ALT+F11)
Private Sub Workbook_Open()
Application.OnTime Worksheets( "Sheet1").Range( "A1").Value, AlertSub
End Sub

And put this in a new Module after you insert it

Sub AlertSub()
MSGBOX "Alert", VBOkOnly
End Sub

Assuming you have the cell A1 in Sheet1 having the time that you want to alert in

Close the Excel file, and reopen it again with macros enabled, then it is done

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 9:14:43 AM
Mon
Jan 8
2007

How can I??.....?

I'm creating a calendar on excel ...how would it be possible to add a background picture to the calendar? .....how do i do it step by step? can anyone please help me??


Answer

You can
1- Put the picture as in (Format > Sheet > Background) and then remove the gridlines from (Tools > Options > Show gridlines)
2- Make the calendar itself as a picture using the camera option in Excel. (You will need to mail me for this help file, I can't attach files here)

In all of these ways, you can use my calendar that I would be proud to give freely, it works for all years all months

take it from the free applications section here
http://shiekhali.com/anmar/projects

Enjoy my profile, I am the VBAXLMan

Monday 1/8/2007 12:55:55 AM

Tags

History