Convert a text range of numbers to individual rows

**TheRetroChief** · 10-22-2018, 09:08 AM

Hello there,

Been scratching my head for a little while about this one. Aside from a time-consuming manual process, what would be the best way of converting one row of data which has a text range of values, e.g. sizes, to duplicated rows for each size?

i.e.

Style Brand Size
0001 ABC12 6-12

Would become:

Style Brand Size
0001 ABC12 6
0001 ABC12 7
0001 ABC12 8
0001 ABC12 9
0001 ABC12 10
0001 ABC12 11
0001 ABC12 12

I've attached a better example than above if required.

In reality, this would need to cover many styles; 0001, 0002, 0003...etc. in a list, not just one.

I don't mind either a formulaic solution or VBA, can't imagine it would be easy to do in a formula mind.

Thanks,
TRC

**Bo_Ry** · 10-22-2018, 09:33 AM

Formula, Please see attached

Please try J2

Formula:

Please Login or Register  to view this content.

M2

Formula:

Please Login or Register  to view this content.

The rest is normal Vlookup

**TheRetroChief** · 10-22-2018, 10:04 AM

Nice, this has worked a treat. I did stumble into problems when I increased the number of items in column A-G, but a bit of trial and error enabled me to amend the new formulae sufficiently.

Many thanks Bo_Ry

**agentanderson** · 09-28-2022, 09:30 PM

Hi, sorry to resurrect this thread, but in my research this was the closest example I could find online. I need to do almost the exact same thing; however, in my case I have cells written as "S-XL" along with other variations like "XS-XL", "XXS-XL", etc. I tried applying the formula generously provided by Bo_Ry with a few customizations but I'm not having any luck. Any advice would be most appreciated.

Convert a text range of numbers to individual rows

LinkBack

Thread Tools

Rate This Thread

Display

Convert a text range of numbers to individual rows

Re: Convert a text range of numbers to individual rows

Re: Convert a text range of numbers to individual rows

Re: Convert a text range of numbers to individual rows

Thread Information

Users Browsing this Thread

Similar Threads

VBA to convert a range from numbers to numbers stored as text (Text to Columns)

Convert data range into individual rows

Convert text to numbers and delete blank rows

Macro to convert date range to individual line items (i.e. rows) in excel

Export Individual Rows To Individual Text Files?

[SOLVED] Convert numbers stored as text to numbers for range of data that changes

convert range of text to numbers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions