Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Taking the last four characters in a string

Hi!

I have an excel sheet where one of the fields is 'Yearspan'. Within this 'Yearspan' field, there are some cells which include data in the format: 'XXXX-XXXX' and others which contain the format 'XXXX', where 'XXXX' is a year, i.e. '2001-2002', or '2002'. An example of this would be...

'Yearspan

2001-2002

2009

2010-2011

2013

2014

2005-2006

2003-2004'

and so on...

I want to create a column that only takes the second year if the format is 'XXXX-XXXX', so if '2001-2002', only take '2002'. If the field is 'XXXX', just to take that year.

The end result needs to be a column which only includes dates in the 'XXXX' format, i.e. just one year.

Is this possible? Thank you!

1 Solution

Accepted Solutions
its_anandrjs

Just write this

=Right(Yearspan,4) as NewFiled

Or

=Right(Yearspan,4)

This will give you below output

2002
2009
2011
2013
2014
2006
2004

View solution in original post

4 Replies
its_anandrjs

Just write this

=Right(Yearspan,4) as NewFiled

Or

=Right(Yearspan,4)

This will give you below output

2002
2009
2011
2013
2014
2006
2004
lukaspuschner
Partner - Creator

Hi O G,

use right(Yearspan,4).

it Returns you the last 4 parts of your stirng.

whiteline
Master II

Hi.

You can use right() function in load script to get the four digits and num#() function to convert the string into number.

num#(right(Yearspan, 4),'0') as Year

Read the QV help about string functions. There are many useful things.

msvanfeldt
Partner - Contributor II

SubField(Yearspan,'-',-1) as LastYear will do the trick.

You can use negative numbers in the SubField function to pick the substrings backwards.