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:

B1: =SUMPRODUCT((CODE(UPPER(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)))<{65,91})*{-1,1})

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

=SUMPRODUCT((CODE(UPPER(MID(E8,ROW($A$1:INDEX($A:$A,LEN(E8),1)),1)))<{65,91})*{-1,1})

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.