Find/replace sequence range of numbers 1-10

[aleon]

Hi all,

New to the forums, keep up the good work.

Getting stuck on getting a cell with a value of 1-10, separated out into its individual values i.e 1,2,3,4,5,6,7,8,9,10
Another example is 50-60 again, i want to separated the values out like: 50,51,52... and so on...

Can anyone help?

I managed to get some info how it might be possible, but gettig stuck on a working formula in Excel 2016.

<code>=SUBSTITUTE(TRIM(CONCAT(ROW(INDIRECT(LEFT(A1,FIND("-",A1)-1)&":"&MID(A1,FIND("-",A1)+1,999)))&" "))," ",",")</code>

Excel complains on the <code>A1,FIND portion where trying to find values separated by a hyphen =-=

Hoe someone can help?

Thanks very much.
</code>

 

[Yongle]

The above formula works correctly for me
Are you entering it as an array formula - committed with {Ctrl}{Shift}{Enter}
Is the value of the cell being interpreted as text?
Is there a non-conventional space or another unseen character in the cell (not all spaces are the same)
Try the manual test that I used and then if that works (it does!) fix the cell value

I entered this value into cell A1 to force it to be accepted as text
'1-10
and formula in B1 returned
1,2,3,4,5,6,7,8,9,10

then tried
'82-109
and formula returned
82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109

 

[Yongle]

I also tested by formatting cells as text and then entering
1-10 and 82-109 into A1
It returned correct strings

Just a further thought - there is more than one hyphen
- there is a short and long hyphen
- that may be what is tripping you up
- delete the one that is in your cell and replace with the normal one and see if that gets the formula working

 

[Yongle]

deleted by yongle

 

[aleon]

Thats weird because still get EXCEL complaining.
Pressing the {Ctrl}{Shift}{Enter} key does not do anything for me, so I am wondering if my excel has this keyboard shortcut disabled?

 

[aleon]

https://imgur.com/a/XZtxc1d
So above is screenshot of what excel is complaining.

 

[Yongle]

so I am wondering if my excel has this keyboard shortcut disabled?

I doubt it, but lets disprove it

Enter this formula into a cell
=Z1+Z2
and commit with {CTRL}{SHIFT}{ENTER}

the formula should now look like this
{=Z1+Z2}

let me know

 

[Yongle]

another thought - which regional settings are you using?
- try this instead (apostrophes instead of commas)
(you may need to correct it if I have missed something)

=SUBSTITUTE(TRIM(CONCAT(ROW(INDIRECT(LEFT(A1;FIND("-";A1)-1)&":"&MID(A1;FIND("-";A1)+1;999)))&" "));" ";",")

 

[aleon]

Well that works, returns 0 (zero) which i suspect is normal.
My region settings (UK) but Format set to EU (Slovenian) as keyboard if from that part of the world.

I doubt it, but lets disprove it

Enter this formula into a cell
=Z1+Z2
and commit with {CTRL}{SHIFT}{ENTER}

the formula should now look like this
{=Z1+Z2}

let me know

 

[aleon]

another thought - which regional settings are you using?
- try this instead (apostrophes instead of commas)
(you may need to correct it if I have missed something)

=SUBSTITUTE(TRIM(CONCAT(ROW(INDIRECT(LEFT(A1;FIND("-";A1)-1)&":"&MID(A1;FIND("-";A1)+1;999)))&" "));" ";",")

As for your suggestion of using Semi Colons to replace the comas now doesnt show reject the equation and fail to accept, just now shows #NAME ?

I will get there one day, I hope

Thanks for your advise Yongle