selecting the last letter in a string

[Guest]

Hello,

I'm working with a school database I created from importing and merging
other peoples files and data. It provides a list of all schools in a region
and the grades they offer. The grades offered column is text and contains
string variables with different lengths, for example,
row 1 = K-12
row 2 = k-6,10-12
row 3 = 1-3, 6-9
row 4 = k-3

I want to create a new column grouping the grades so I can group them into
primary, elementary and so on. So, the new column would look like

Row 1 K-12
Row 2 K-12
Row 3 1-9
Row 4 k-3

Basically I want to select the first and last character in the string. How
can I do this if the length of strings vary from row to row?

Thanks for any and all help. I'm completely stuck.

 

[Pete_UK]

You can use:

=LEFT(A1,1)
and
=RIGHT(A1,1)

to get the first and last characters of a string, though I'm not sure
how this will help you.

Hope this helps.

Pete

 

[MartinW]

Hi,

Try this in B1 and dragged down as far as is needed.
=LEFT(A1,1)&" - "&IF(RIGHT(A1,1)*1<3,RIGHT(A1,2),RIGHT(A1,1))

This should work for your data as posted, however,
if it is possible that your last figure could be a 1 or a 2
on it's own, then the formula will need some adjusting.

HTH
Martin