Using ASC and LEFT to get the first letter in Cell

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hi Folks,
I'm trying to use ASC and LEFT to get teh first letter in the cell and
evaluate if it is below a certain range e.g. a > g, h > n etc and assign a
value from another cell depending on the outcome.

Can anyone tell me how to do this in a Cell Function please? I can do this
in VB or VBA no problem but not in Cell.

Cheers

J
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=MID(A1,MIN(IF(ISERROR(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(INDIREC
T("1:1024")))),1)

or to make this a more readable try:
- add a defined name (call it 'seg') with the formula
=ROW(INDIRECT("1:1024"))

and use the array formula
=MID(A1,MIN(IF(ISERROR(--MID(A1,seq,1)),seq)),1)
 
=IF(AND(LEFT(A1,1)>"h",LEFT(A1,1)<"n"), "Match","No match")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
JB said:
I'm trying to use ASC and LEFT to get teh first letter in the cell and
evaluate if it is below a certain range e.g. a > g, h > n etc and assign a
value from another cell depending on the outcome.
Click to expand...
....

Depending on how many ranges you have, and whether you'd have one value (1,
say) for a letter between a and g, another (5) between h and n, yet another
(8) for o to u, and finally one other (9) for v to z. If so, lookups are the
better way to do this.

=LOOKUP(LEFT(x,1),{"a","h","o","v"},{1,5,8,9})
 
Cheers Bob! That done it. I had a look at Frank's reply but couldn't
really understand it to be honest.

Cheers

J
 
Back
Top