Combine text and formula?

[Curalice]

In Col A I have number strings that indicate the year in either the first 2 or the first 3 numbers
204359687456 - 2004 year
982356441445 - 1998 year
I can use MID and LEFT to get 04 and 98 in Col B, but I want to add the 'prefix" of 20 or 19 so that the list will sort in the Worksheet column and Pivot Table row.
Any help is appreciated.

[CLR]

=IF(LEFT(A1)="2",20&MID(A1,2,2),LEFT(A1,2))*1

Vaya con Dios,
Chuck, CABGx3



"Curalice" <[email protected]> wrote in
message news:[email protected]...
>
> In Col A I have number strings that indicate the year in either the
> first 2 or the first 3 numbers
> 204359687456 - 2004 year
> 982356441445 - 1998 year
> I can use MID and LEFT to get 04 and 98 in Col B, but I want to add the
> 'prefix" of 20 or 19 so that the list will sort in the Worksheet column
> and Pivot Table row.
> Any help is appreciated.
>
>
> --
> Curalice
> ------------------------------------------------------------------------
> Curalice's Profile:
http://www.excelforum.com/member.php...o&userid=28600
> View this thread: http://www.excelforum.com/showthread...hreadid=502725
>

[Curalice]

In Col A I have number strings that indicate the year in either the
first 2 or the first 3 numbers
204359687456 - 2004 year
982356441445 - 1998 year
I can use MID and LEFT to get 04 and 98 in Col B, but I want to add the
'prefix" of 20 or 19 so that the list will sort in the Worksheet column
and Pivot Table row.

the formula =IF(LEFT(A1)="2",20&MID(A1,2,2),LEFT(A1,2))*1
worked for the 2004 year, THANKS!, now I am puzzed as to how to use it for the 1998 year.

Many thanks.

[Curalice]

Ha, I got it
=IF(LEFT(C23453)="9",19&LEFT(C23453,2))*1

[CLR]

Sorry, I went to bed early last night and missed your post asking about the
"1998".....I had it but guess I posted the older version,.....but the best
thing is, you figured it out yourself!.......and that's really what it's all
about......congratulations!

Vaya con Dios,
Chuck, CABGx3


"Curalice" <[email protected]> wrote in
message news:[email protected]...
>
> Ha, I got it
> =IF(LEFT(C23453)="9",19&LEFT(C23453,2))*1
>
>
> --
> Curalice
> ------------------------------------------------------------------------
> Curalice's Profile:
http://www.excelforum.com/member.php...o&userid=28600
> View this thread: http://www.excelforum.com/showthread...hreadid=502725
>