Formula for Separating Digits after nth Digit

[Excel1991]

Hi all,

I have a list of codes which can be of varying lengths ("99999" or "99999-25" or "99999R") and I would like to take the put the first 5 digits in one column and whatever comes after the first 5 digits in the net column?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Original Code[/TD]
[TD]First 5[/TD]
[TD]Trailing Character[/TD]
[/TR]
[TR]
[TD]99999[/TD]
[TD]99999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]99999-25[/TD]
[TD]99999[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]99999R[/TD]
[TD]99999[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]

I dont think text to columns would work because i do not have a common deliminator. Does anyone know of a formula that can be used in this instance? Thank you

 

[DanteAmor]

It can be text to columns but with Fixed Width / Next / Create a break line after character 5.

 

[DanteAmor]

Or try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:115.01px;" /><col style="width:76.04px;" /><col style="width:170.14px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Original Code</td><td >First 5</td><td >Trailing Character</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">99999</td><td style="text-align:right; ">99999</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >99999-25</td><td style="text-align:right; ">99999</td><td style="text-align:right; ">-25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >99999R</td><td style="text-align:right; ">99999</td><td >R</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=LEFT(A2,5)</td></tr><tr><td >C2</td><td >=MID(A2,6,LEN(A2)-5)</td></tr></table></td></tr></table>

 

[Scott Huish]

It can be text to columns but with Fixed Width / Next / Create a break line after character 5.

That wouldn't strip out the - and it doesn't exist in all of them.

Perhaps:

Excel 2010
	A	B	C
1	Original Code	First 5	Trailing Character
2	99999	99999
3	99999-25	99999	25
4	99999R	99999	R
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,5)
B3	=LEFT(A3,5)
B4	=LEFT(A4,5)
C2	=MID(SUBSTITUTE(A2,"-",""),6,255)
C3	=MID(SUBSTITUTE(A3,"-",""),6,255)
C4	=MID(SUBSTITUTE(A4,"-",""),6,255)

 

[DanteAmor]

Hi @Excel1991, In your example you deleted the "-". Do you need to remove the "-"? Besides the "-" is there any other character you want to remove?

 

[Excel1991]

It can be text to columns but with Fixed Width / Next / Create a break line after character 5.

Really not sure how I overlooked this. Thank you though! The formulas also work exactly as planned. I appreciate your help

 

[Excel1991]

If it is possible to remove them, then that is preferable, but the two methods you provided are also sufficient

 

[Excel1991]

That wouldn't strip out the - and it doesn't exist in all of them.

Perhaps:

Excel 2010

	A	B	C
Original Code	First 5	Trailing Character
99999-25
99999R	R

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]99999[/TD]
[TD="align: right"]99999[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]99999[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=LEFT(A2,5)[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A2,"-",""),6,255)[/TD]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"]=LEFT(A3,5)[/TD]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A3,"-",""),6,255)[/TD]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"]=LEFT(A4,5)[/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A4,"-",""),6,255)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Could you explain how the "255" portion of the formula works?

 

[Scott Huish]

It just means 255 characters. It ensures that you get, in all likelihood, however many characters you have in your string.

 

[DanteAmor]

Really not sure how I overlooked this. Thank you though! The formulas also work exactly as planned. I appreciate your help

I'm glad to help you. Thanks for the feedback.