CODEWITHSUNDEEP
excelexcel-formulavba
Kay Cee
Kay Cee

Reputation: 341

Split a cell into varying numbers of cells - Excel

Apologies if a similar question has been answered. I did search, but could not find what I was looking for.

I have a spreadsheet into which I have copied and pasted an amount of data about various types of beer. I want to split strings single cells containing text into an arbitrary number of cells corresponding to the type(s) of beer and the alcohol percentage. The thing I'm having trouble with is that some beers have two or more categories, whereas most have only one.

This is an example of the text strings I'm trying to split: 

American Pale Ale (APA) / 6.40% ABV
Quadrupel (Quad) / 10.20% ABV
American Double / Imperial IPA / 8.00% ABV
American Wild Ale / 7.75% ABV

The intended result is:

Category 1              | Category 2   | Alcohol %

American Pale Ale (APA) |              | 6.40% ABV
Quadrupel (Quad)        |              | 10.20% ABV
American Double         | Imperial IPA | 8.00% ABV
American Wild Ale       |              | 7.75% ABV

For beers with one category only I've been using the following formula:

=RIGHT(D3,LEN(D3)-SEARCH("/",D3,1))

However, I'd like something that will work no matter how many categories the beer has. I feel like it must be possible seeing as there is a common separator (/).

Can anyone help?

Upvotes: 2

Views: 1012

Answers (2)

G one
G one

Reputation: 2729

Instead of using a macro, you can also use Text-to-column option which is inbuilt in Excel. Goto Datatab-> Text-to-columns

A dialog box will pop out. In that select delimited radio button and click next. Then specify the delimiter by which you wanna separate the text and click next and finish. You would get your desired result.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

Let's say your workbook looks like this

enter image description here

Try this code. I have commented the code so that you will not have a problem understanding it.

Option Explicit

Sub Sample()
    Dim MYAr
    Dim ws As Worksheet, wsOutput As Worksheet
    Dim Lrow As Long, i As Long, j As Long, rw As Long, SlashCount As Long, col As Long

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> get the last row
        Lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Count the max number of "/" in a particular cell
        '~~> This will decide the span of the columns
        For i = 1 To Lrow
            MYAr = Split(.Range("A" & i).Value, "/")
            If UBound(MYAr) + 1 > SlashCount Then SlashCount = UBound(MYAr) + 1
        Next i

        '~~> Add a new worksheet for output
        Set wsOutput = ThisWorkbook.Sheets.Add
        rw = 1

        For i = 1 To Lrow
            MYAr = Split(.Range("A" & i).Value, "/")

            col = 1
            '~~> Write values to column. We will not write the
            '~~> Last value of the array here
            For j = LBound(MYAr) To (UBound(MYAr) - 1)
                wsOutput.Cells(rw, col).Value = MYAr(j)
                col = col + 1
            Next j
            '~~> Write Last value of the array to the last column
            wsOutput.Cells(rw, SlashCount).Value = MYAr(UBound(MYAr))
            rw = rw + 1
        Next i
    End With
End Sub

OUTPUT

enter image description here

Upvotes: 3

Related Questions