Word Formating & Words Adding
 

i) In column A is my product series as "18-65-85", "18-1-98", "18-656-74" and
etc.
Now, I would like make it all in standard as "18-0065-85", "18-0001-98"
"18-065
6-74" and etc. What's faster way?

ii)Now in column B is my product code as "PO-484P", "PP-65P", "HB-K5P", etc.
Now, I would like to add an "A" in every code to be "APO-484P",
"APP-65P", "AHB-
K5P", etc... What's faster way?

Word Formating & Words Adding
 

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