Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count letter string, e.g. h/EL/p | Excel Worksheet Functions | |||
How to count the match letter within a string? | Excel Discussion (Misc queries) | |||
New Validation option to format 1st letter as Capital letter | Excel Discussion (Misc queries) | |||
How can I count the number of times a letter repeats in a string? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) |