Generate series with suffix and prefix but middle change

feroz_bilal

New Member
Joined
Jul 3, 2015
Messages
27
Hello
i want to make a list of my products in this way upto 600:

Fbtr-1-s
fbtr-1-m
fbtr-1-l
fbtr-1-xl

fbtr-2-s
fbtr-2-m
fbtr-2-l
fbtr-2-xl

now here fbtr- is prefix and there are four suffix i.e. s,m,l,xl...
i want to change only numeric part 1 to 600.

please guide me.
Regards
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Does this formula (copied down) do what you want...

=IF(MOD(ROWS($1:1),5)>0,"fbtr-"&1+INT((ROWS($1:1)-1)/5)&CHOOSE(1+MOD(ROWS($1:1)-1,5),"-s","-m","-l","-xl"),"")
 
Upvote 0
Thank u sir for prompt reply.
The formula is working but how can i make a series from 1 to 600 and also i want repetition of numbers 4 times with different sizes. Kindly look to this link, i want exactly the same.
http://i.imgur.com/Hgc5pCK.png
Regards
What you said you wanted in Message #1 is different than what you show in your picture (no blank rows between groups, numbers are three digits long)... you should have showed us the picture first. Here is the new formula and instructions how to create list with it...

1) Type A1:A1200 in the Name Box (the field to the left of the Formula Bar) and then press Enter. This will select 1200 cells in Column A.

2) Copy/Paste this formula into the Formula Bar...

="FBTR-"&TEXT(1+INT((ROWS($1:1)-1)/4),"000")&CHOOSE(1+MOD(ROWS($1:1)-1,4),"-S","-M","-L","-XL")

3) Commit the formula using CTRL+ENTER (note, use only those two keys).
 
Upvote 0
What you said you wanted in Message #1 is different than what you show in your picture (no blank rows between groups, numbers are three digits long)... you should have showed us the picture first. Here is the new formula and instructions how to create list with it...

1) Type A1:A1200 in the Name Box (the field to the left of the Formula Bar) and then press Enter. This will select 1200 cells in Column A.

2) Copy/Paste this formula into the Formula Bar...

="FBTR-"&TEXT(1+INT((ROWS($1:1)-1)/4),"000")&CHOOSE(1+MOD(ROWS($1:1)-1,4),"-S","-M","-L","-XL")

3) Commit the formula using CTRL+ENTER (note, use only those two keys).


Thank you very much sir...Really thanks a lot.
I was stuck in this..
This worked very well....
Regards
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top