Help with formula

[Russk68]

Hi All,
I need to enter values in a cell similar to this 33/35/56/65. It can be single digits or up to 4 digits and max of 20 slashes. I would then like the next 20 cells below to be able to fill in each number separately.

Example:
33/35/56/65
Result:
33
35
56
65
Your help is greatly appreciated!

Russ

 

[oldbrewer]

33/357/5678/65

33

357

5678

65

this macro has been applied

Sub Macro4()

'

' Macro4 Macro

' Macro recorded 12/11/2018 by bob

'

'

rrow = 1

mytarget = Cells(1, 1)

50 For j = 1 To 10

If Mid(mytarget, j, 1) = "/" Then temp = Mid(mytarget, 1, j - 1): rrow = rrow + 1: mytarget = Mid(mytarget, j + 1, 999): Cells(rrow, 1) = temp: GoTo 50

Next j

Cells(rrow + 1, 1) = mytarget

100 End Sub

<colgroup><col><col span="20"></colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

Here's a formula solution.
Assuming data in A1, formula in A2 copied down to A21 or as far as needed (Formula results in Text, Not real numbers, in case you have data with leading zero).
Alternatively, use B2 formula, results converted to Real Numbers, Leading zeros, if any, will be removed:

Book1
	A	B
1	33/35/56/0101/65	33/35/56/0101/65
2	33	33
3	35	35
4	56	56
5	0101	101
6	65	65
Sheet357

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1,"/",REPT(" ",255)),ROWS(A$2:A2)*255-254,255))
B2	=IFERROR(MID(SUBSTITUTE(B$1,"/",REPT(" ",255)),ROWS(B$2:B2)*255-254,255)+0,"")

 

[Russk68]

Thank you very much!

 

[Russk68]

Hi J
I just used your B2 formula and dragged to B21 and it works just like I asked. I'm trying to repeat it in the same column. I entered your formula in B23 and updated it to: =IFERROR(MID(SUBSTITUTE(B$22,"/",REPT(" ",255)),ROWS(B$23:B23)*255-254,255)+0,"") and dragged it down. I does not work when I do this. Should it?
Thank you very much for your help!

 

[Russk68]

Works great! My bad!
Thank you so much J!

 

[jtakw]

You're welcome, great it worked for you, let us know if you need further help.