Kelvin,

There is probably an easier formula or a custom number format that may
work but this is what I came up with.

Question 1

In cell B2

=IF(FIND("-",A2,4)=5,CHOOSE(FIND("-",A2,4)-4,LEFT(A2,3)&"000"&RIGHT(A2,4),LEFT(A2,3)&"00"&RIG HT(A2,5),LEFT(A2,3)&"0"&RIGHT(A2,6),A2))

Drag down column B. I am assuming that the number between the hyphens
will only be 1, 2, 3, or 4 digits long.

Question 2.

Where A16 is your text.

="A"&A16

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=488759