Formula to convert or make to 3 digit always

[kumar1803]

Hello,

Please help me convert the following number in the format provided below:

ALCET1010036303-15


Should be:

ALCET1010036303-015

AND

ALCET1010036580-3

Should be:

ALCET1010036580-003

 

[jtakw]

Hi,

Something like this?

Excel 2010
	A	B	C	D
1	ALCET1010036303-15	ALCET1010036303-015		ALCET1010036303-015
2	ALCET1010036580-3	ALCET1010036580-003		ALCET1010036580-003
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,SEARCH("-",A1))&TEXT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)),"000")
D1	=LEFT(A1,16)&TEXT(RIGHT(A1,LEN(A1)-16),"000")

Use C column formula if value left of hyphen is variable in length.
Use D column formula if value left of hyphen is always 15 characters.

 

[Rick Rothstein]

Assuming there are always the same number of characters before the dash (like in your two examples), then this formula should do what you want...

=REPLACE(A1,17,3,TEXT(MID(A1,17,3),"000"))

 

[FDibbins]

Rick, I was trying (without much luck) to see if there was a custom format to do this?

 

[jtakw]

Rick, I was trying (without much luck) to see if there was a custom format to do this?

I was looking into it too...maybe someone can post the custom format.

 

[Rick Rothstein]

Rick, I was trying (without much luck) to see if there was a custom format to do this?

You can only format cells containing numbers in Excel (well, theoretically you can format text, but only by adding more text via the custom format)... the OP's cells contain a mix of text and numbers meaning Excel sees the cell as containing text. If the OP wants to physically change the cells themselves (rather than having the fixed text in another cell), then we would need to use VB code to do that.

 

[jtakw]

You can only format cells containing numbers in Excel (well, theoretically you can format text, but only by adding more text via the custom format)... the OP's cells contain a mix of text and numbers meaning Excel sees the cell as containing text. If the OP wants to physically change the cells themselves (rather than having the fixed text in another cell), then we would need to use VB code to do that.

Thanks Rick for letting us know.

 

[FDibbins]

Thanks for the feedback Rick, thought I was missing something