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]

If you are on UK regional setting semi-colons are not the problem
You can prove it by entering any value into A1 and putting this formula in any cell
=MID(A1,1,1)
If the formula returns anything then commas are ok in your formula

I will get there one day, I hope

I am glad you are a glass-half-full type!

Did you try...
in A1 entering..
'1-10
and in B1 this formula..
=SUBSTITUTE(TRIM(CONCAT(ROW(INDIRECT(LEFT(A1,FIND("-",A1)-1)&":"&MID(A1,FIND("-",A1)+1,999)))&" "))," ",",")

PLEASE copy and paste EVERYTHING from here as I know this works

 

[Yongle]

If that fails
Ensure that A1 contains..
'1-10 (including apostrophe)
and then in B1 enter this formula..
=FIND("-",A1)

B1 returns a number

 

[Fluff]

Do you have a 365 subscription?

 

[aleon]

If that fails
Ensure that A1 contains..
'1-10 (including apostrophe)
and then in B1 enter this formula..
=FIND("-",A1)

B1 returns a number

Thanks again Yongle, still problem accepting the new array function from your previous post or even the simplified version as you can see form screenshot.

Excel 2016 flags up another error.
https://imgur.com/a/T0FgMuP

My colleague also trie don his own machine and get same problems.
This is really weird...

So am gona try on another machine which hopefully has older version of excel i can try.

 

[aleon]

Do you have a 365 subscription?

No I dont, but sysadmin has, why?

 

[Fluff]

I believe that the CONCAT function is only available with a 365 subscription.

 

[aleon]

I believe that the CONCAT function is only available with a 365 subscription.

Thanks Fluff.
Have now updated the formula to replace CONCAT to CONCATENATE

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

This partly works, but only shows first character int he series of number, in this case just 1.

 

[Peter_SSs]

Have now updated the formula to replace CONCAT to CONCATENATE

CONCATENATE requires you to enter individually all the values to be concatenated & all the delimiters, so I don't think it is going to meet your needs.

You mentioned excel 2016 in your first post but if you have that but not CONCAT or TEXTJOIN then it must be a stand-alone version of Office, not Office 365.
If you don't have the CONCAT function (or this could be done with a shorter formula using TEXTJOIN but that function also required Office 365) then ..

I would suggest that you consider a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Seq(sLimits As String, Optional sDelimiter As String = ",") As Variant
  Seq = Join(Application.Transpose(Evaluate("row(" & Replace(sLimits, "-", ":") & ")")), sDelimiter)
End Function

Note that the function offers an optional different delimiter as shown in cell C2 (though for your language version you may need to use a semicolon =Seq(A2;"|")

Excel Workbook
	A	B	C
1	1-10	1,2,3,4,5,6,7,8,9,10
2	50-60	50,51,52,53,54,55,56,57,58,59,60	50|51|52|53|54|55|56|57|58|59|60
Sequence

 

[aleon]

@Peter_SSs

Thats excellent, this works for me

I am using MS Office Professional Plus 2016 MSO (16.0.4738.1000) 32-bit. Working with it on a Win 10 64-bit Laptop.

Thank you very much, also thanking everyone else involved in this thread, much appreciated your advise.

 

[Peter_SSs]

No problem. Glad to help.

BTW, Welcome to the MrExcel board!


For other readers, here it is with the TEXTJOIN function

Excel Workbook
	A	B
1	1-10	1,2,3,4,5,6,7,8,9,10
2	50-60	50,51,52,53,54,55,56,57,58,59,60
Sequence