counting text characters within a cell

[jat82nd]

This should be simple, but I am struggling with finding a way to use the search or find function to identify text characters. This is my situation, I have for example a cell that contains FW023 or D1234. I need to be able to count the number of characters that are text.

i would think I would be able to do it with the search or find function, but can't figure out how to get it to just count the number of text characters with in it.

Can someone help?

Thanks in advance

[Ron Coderre]

With
A1 containing text and/or numbers

This formula returns the count of A-Z, a-z characters:

Please Login or Register  to view this content.

Is that something you can work with? or do you also need to count other non-numeric charactes?

[jat82nd]

Thanks Ron. That worked, but I think it was a bit more then what I needed. I only needed it to look at 1 cell, not a column. I tried to modify your formala, but didn't have any luck, because I don't seem to understand what it is doing.

Can you help? The cell is E8

[Ron Coderre]

Here you go:

This formula returns the text-char count from cell E8

Please Login or Register  to view this content.

Notes:
There are quite a few "moving parts" in that formula, so I'll explain some of it.

Since we don't know what the length of the text in E8 will be, this section:

ROW($A$1:INDEX($A:$A,LEN(E8)

creates a range reference of vertical cells, beginning with A1 and continuing down for the number of characters that are in E8.

So if E8 contains A123B (5 characters), then the range will be A1:A5.
The ROW function returns the list of RowNumbers in the range:
{1,2,3,4,5}

The MID function plucks out each E8 character referenced by that array as if it were written this way:

MID(E8,1,1).......A
MID(E8,2,1).......1
MID(E8,3,1).......2
MID(E8,4,1).......3
MID(E8,5,1).......B

Each character is converted to upper-case and its ASCII code is tested to see if it in the range of values that represents A thru Z.

Hits =1, Misses = 0

SUMPRODUCT adds them up.

I hope that helps.

[jat82nd]

Thanks Ron, that was a great help. Saved me a ton of time.... Would you also know how to get excel to associate a text to a file such as a picture?

For example: I have drop down menu with a list of part number. I want it to be able to bring up a picture of the part # I choice. Lets say i choice an MC-1200. I want it to be able to bring up a picture associated with that part number.

Excel may not be able to do it without a macro, but there is always hope... :-)

thanks again

[Ron Coderre]

How did we go from counting text characters to displaying product pictures???

Next time.....new thread, ok?

Anyway....since I'm already here:

The VBA solution provided by JE McGimpsey is the generally accepted best approach:
http://www.mcgimpsey.com/excel/lookuppics.html

This is just an alternative non-VBA solution, in case you don't want to use programming:

Assumption: Pictures are stored on Sheet2 to be dynamically shown on Sheet1.

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture and put it in the sheet).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text:
Example for a picture of an Elephant:
Insert>Name>Define
Name: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the items.

3)Create a dynamic range name that refers to that cell:
Insert>Name>Define
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
...or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With the picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic

The picture will be replaced by the picture referred to by the dropdown list.

Each time you select a different item in the list, the associated picture will appear in the picture box and resize appropriately.

[jat82nd]

Thanks... sorry about not posting a new thread. I kind of thought about it after I posted it. Wasn't something I was actually planning on doing, but the idea just pop into my head, so I thought I would just ask the experts if it could be done. It is actaully pretty simple... once you figure it out. Although I think if I have to many of them, then it will overload the program to where it will slow. How many pictures do you think you can put into one excel program?

[Ron Coderre]

I expect that the volume of pictures would only effect a performance degradation if they consumed so much memory that your computer bogged down swapping between ram and disk to perform other tasks.

I hope that helps.

[jat82nd]

Can it be done as a hyperlink, instead of displaying the picture? That might give me the same effect, without bogging down my system.

Thanks for all your help though.

[Ron Coderre]

1) Hyperlinked files open in the application they're associated with: xls: Excel, DOC: word, etc. Consequently, users won't be seeing them in Excel.

2) You're talking about small images that give users a peek at what the item is. I wouldn't worry about the files bogging down your system. It's the workbooks that are chock full of volatile functions that are a bigger problem.