将细胞分解成不同数量的细胞 - Excel [英] Split a cell into varying numbers of cells - Excel

问题描述

如果已经回答了类似的问题，请对此表示歉意。我没有搜索，但找不到我要找的东西。

我有一个电子表格，我已经复制并粘贴了大量关于各种啤酒的数据。我想将包含文本的单个单元格分割成与啤酒类型和酒精百分比对应的任意数量的单元格。我遇到麻烦的是，一些啤酒有两个或更多个类别，而大多数啤酒只有一个。

这是我的文本字符串的一个例子尝试拆分：

 美式淡啤酒（APA）/ 6.40％ABV 
四重（Quad）/ 10.20％ABV 
美国双/英国IPA / 8.00％ABV 
美国野生啤酒/ 7.75％ABV 
  

预期的结果是：

 类别1 |类别2 |酒精％
 
美式淡啤酒（APA）| | 6.40％ABV 
 Quadrupel（Quad）| | 10.20％ABV 
 American Double |帝国IPA | 8.00％ABV 
 American Wild Ale | | 7.75％ABV 
  

只有一个类别的啤酒我一直在使用以下公式：

  = RIGHT（D3，LEN（D3）-SEARCH（/，D3,1））
  

但是，无论啤酒有多少类别，我都会喜欢这些工作。我觉得有必要看到，因为有一个常见的分隔符（/).

任何人都可以帮助？

解决方案

让我们说你的工作簿看起来像这样

尝试此代码。我已经评论过这个代码，所以你不会理解它。

  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 
 
'~~>将此设置为相关工作表
设置ws = ThisWorkbook.Sheets（Sheet1）
 
带有ws 
'~~>得到最后一行
 Lrow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 
'~~>计算特定单元格中的/的最大数量
'~~>这将决定列的跨度
对于i = 1 To Lrow 
 MYAr = Split（.Range（A& i）.Value，/）
如果UBound MYAr）+ 1> SlashCount Then SlashCount = UBound（MYAr）+ 1 
 Next i 
 
'~~>为输出添加一个新的工作表
设置wsOutput = ThisWorkbook.Sheets.Add 
 rw = 1 
 
对于i = 1 To Lrow 
 MYAr = Split（.Range A& i）.Value，/）
 
 col = 1 
'~~>将值写入列。我们不会写
'~~>数组的最后一个值
对于j = LBound（MYAr）To（UBound（MYAr） -  1）
 wsOutput.Cells（rw，col）.Value = MYAr（j）
 col = col + 1 
下一步j 
'~~>将数组的最后一个值写入最后一列
 wsOutput.Cells（rw，SlashCount）.Value = MYAr（UBound（MYAr））
 rw = rw + 1 
 Next i 
结束
 End Sub 
  

OUTPUT

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?

解决方案

Let's say your workbook looks like this

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

这篇关于将细胞分解成不同数量的细胞 - Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！