Etract numbers from a string of 13 digits into 5 separate cells

[Kazdima]

Hello,
i need do extract digits from One cell into 5 separate.

<colgroup><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> </colgroup><tbody>
[TD="class: xl69, width: 449, colspan: 6"][/TD]

[TD="class: xl72"]A[/TD]
[TD="class: xl73"]B[/TD]
[TD="class: xl73"]C[/TD]
[TD="class: xl73"]D[/TD]
[TD="class: xl73"]E [/TD]
[TD="class: xl74"]F[/TD]

[TD="class: xl75"]2digits[/TD]
[TD="class: xl66"]3digits[/TD]
[TD="class: xl66"]1digit[/TD]
[TD="class: xl66"]3digits[/TD]
[TD="class: xl66"]4digits[/TD]
[TD="class: xl76"]13digits string[/TD]

[TD="class: xl77"]01[/TD]
[TD="class: xl68"]259[/TD]
[TD="class: xl68"]6[/TD]
[TD="class: xl68"]310[/TD]
[TD="class: xl68"]7510[/TD]
[TD="class: xl78"]0125963107510[/TD]

[TD="class: xl79"] [/TD]

[TD="class: xl80"] [/TD]

[TD="class: xl81, colspan: 6"]Note: "Zero" can be in any part of new strings. Not to lose it.[/TD]

</tbody>

Thank you for a help with a formula, and/or Macro.

 

[jtakw]

Hi,

Will this work for you:

Excel 2010
	A	B	C	D	E	F
1	2digits	3digits	1digit	3digits	4digits	13digits string
2	01	259	6	310	7510	0125963107510
Sheet14

Cell Formulas
Range	Formula
A2	=LEFT(F2,2)
B2	=MID(F2,3,3)
C2	=MID(F2,6,1)
D2	=MID(F2,7,3)
E2	=RIGHT(F2,4)

 

[JoeMo]

Here's an alternative where the number of digits you want to extract is in row 1.

Excel Workbook
	A	B	C	D	E	F
1	2	3	1	3	4
2	01	259	6	310	7510	0125963107510
Sheet3

 

[jtakw]

And if OP can add a Blank column in front, one formula can do all five:

Excel 2010
	A	B	C	D	E	F	G
5		digits	digits	digits	digits	digits
6		2	3	1	3	4	13digits string
7		01	259	6	310	7510	0125963107510
Sheet14

Cell Formulas
Range	Formula
B7	=MID($G7,SUM($A6:A6,1),B6)

B7 formula copied across to F7

 

[Rick Rothstein]

And if OP can add a Blank column in front, one formula can do all five:

Excel 2010
	A	B	C	D	E	F	G
5		digits	digits	digits	digits	digits
6		2	3	1	3	4	13digits string
7		01	259	6	310	7510	0125963107510
Sheet14

Cell Formulas
Range	Formula
B7	=MID($G7,SUM($A6:A6,1),B6)

B7 formula copied across to F7

If your worksheet is listing the number of digits per cell as jtakw has assumed above, then use his formula. However, if you are not listing the digit count on the worksheet directly, then you can use the following single formula. With your number in cell F2, put this formula in cell A2 and copy it across to cell E2...

=MID($F2,LOOKUP(COLUMNS($A:A),{1,2,3,4,5},{1,3,6,7,10}),LOOKUP(COLUMNS($A:A),{1,2,3,4,5},{2,3,1,3,4}))

 

[Rick Rothstein]

If your worksheet is listing the number of digits per cell as jtakw has assumed above, then use his formula. However, if you are not listing the digit count on the worksheet directly, then you can use the following single formula. With your number in cell F2, put this formula in cell A2 and copy it across to cell E2...

=MID($F2,LOOKUP(COLUMNS($A:A),{1,2,3,4,5},{1,3,6,7,10}),LOOKUP(COLUMNS($A:A),{1,2,3,4,5},{2,3,1,3,4}))

This is probably a more straightforward formula to use...

=MID($F2,CHOOSE(COLUMNS($A:A),1,3,6,7,10),CHOOSE(COLUMNS($A:A),2,3,1,3,4))

 

[Kazdima]

Thank you everybody for replies. All your formulas are working-)

 

[markmzz]

Hi!

Another way (with JoeMo's layout). In A2 and copy to the right

=LEFT(REPLACE($F2,1,SUM($A1:$E1)-SUM(A1:$E1),""),A1)

Markmzz

 

[Peter_SSs]

. formula, and/or Macro.

Assuming data starting in F2 you could also try this 1-line macro.

Sub TTC()
  Range("F2", Range("F" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(2, 2), Array(5, 2), Array(6, 2), Array(9, 2))
End Sub

 

[Kazdima]

Thank you, Peter. Your Macro is working good.