Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default selecting the last letter in a string

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.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 8,856
Default selecting the last letter in a string

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

On Nov 22, 5:09 pm, csr1176 wrote:
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.


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 860
Default selecting the last letter in a string

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


"csr1176" wrote in message
...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count letter string, e.g. h/EL/p Lee Excel Worksheet Functions 16 September 4th 07 11:57 AM
How to count the match letter within a string? Eric Excel Discussion (Misc queries) 7 August 22nd 07 03:43 AM
New Validation option to format 1st letter as Capital letter Jeff Excel Discussion (Misc queries) 5 July 13th 06 05:11 AM
How can I count the number of times a letter repeats in a string? Wiley Excel Worksheet Functions 3 May 11th 06 06:53 PM
column header changed from letter to number, how return to letter Ron Excel Discussion (Misc queries) 2 May 9th 05 08:34 PM


All times are GMT +1. The time now is 07:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"