Excel Formatting Formula

[lorenzoc]

Good day everyone and thanks for the help,

how do I write a formula that will change these numbers [TABLE="width: 136"]
<tbody>[TR]
[TD]
102080704807W400

into this

[TABLE="width: 223"]
<tbody>[TR]
[TD]102/08-07-048-07W4/00

These are well unit numbers and
[/TD]
[/TR]
[TR]
[TD]I have about 900 of them. The pattern of / and - are always the same.


thanks,

Lorenzo
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

theres gotta be a better way....

but if the ID number length is consistent you can try

=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A1,4,0,"/"),7,0,"-"),10,0,"-"),14,0,"-"),19,0,"/")

 

[hiker95]

lorenzoc,

Something like this?

Excel 2007
	A	B
1	102080704807W400	102/08-07-048-07W4/00
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,3)&"/"&MID(A1,4,2)&"-"&MID(A1,6,2)&"-"&MID(A1,8,3)&"-"&MID(A1,11,4)&"/"&MID(A1,15,2)

 

[etaf]

[TABLE="class: cms_table, width: 136"]
<tbody>[TR]
[TD]102080704807W400
[TABLE="class: cms_table, width: 223"]
<tbody>[TR]
[TD]102/08-07-048-07W4/00[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
Assuming the number start in Cell A2
=MID(A2,1,3)&"/"&MID(A2,4,2)&"-"&MID(A2,7,2)&"-"&MID(A2,8,3)&"-"& MID(A2,11,4)&"/"&MID(A2,15,2)

 

[lorenzoc]

thanks everyone!

All three formulas worked. You guys are great.

take care,

Lorenzo

 

[lorenzoc]

thanks Hiker95,

great example and help! will try and pay it forward!


Lorenzo

 

[Rick Rothstein]

Assuming the values you are converting are always the same (12 digits, a letter, 3 more digits), then here is another formula to consider...

=TEXT(LEFT(A1,12),"000\/00-00-000-00")&MID(A1,13,1)&TEXT(RIGHT(A1,3),"0\/00")

 

[hiker95]

lorenzoc,

Thanks for the feedback.

Your are very welcome. Glad we could help.

And, come back anytime.

 

[Rick Rothstein]

Assuming the values you are converting are always the same (12 digits, a letter, 3 more digits), then here is another formula to consider...

=TEXT(LEFT(A1,12),"000\/00-00-000-00")&MID(A1,13,1)&TEXT(RIGHT(A1,3),"0\/00")

I mananged to reduce down from 5 total function calls to 4 total function calls...

=SUBSTITUTE(TEXT(REPLACE(A1,13,1,"."),"000\/00-00-000-00.0\/00"),".",MID(A1,13,1))

or alternately (still 4 total function calls)...

=REPLACE(TEXT(REPLACE(A1,13,1,"."),"000\/00-00-000-00.0\/00"),17,1,MID(A1,13,1))